|re: I forsee a problem here, because if you add rows after row 40, then the other row numbers will change accordingly, will that not effect the formulas?|
Depending on how the formulas are constructed, it may or may not effect the formulas. Excel has provisions for allowing rows/columns to be inserted/deleted and letting the formulas update accordingly. If Excel didn't allow for those types of changes...well, can you imagine having to update every formula - manually - whenever you made that type of change? So no, that may not be an issue. You could manually insert a row right now and see if it causes a problem.
The bigger issue in my mind, and the reason I asked for more details into your requirements, is because you said essentially what I expected you to say.
"I would need to be able to do the same at rows 79, 118, 157, 196, 235, 274, 313, 352, 391, 430 and 469 "
So the client enters data in Row 40, a new Row 41 appears and Row 79 becomes Row 80, Row 118 becomes Row 119, etc. So now that code has to deal with changes to Row 80, not 79 and Row 119, not 118, etc. But, as they say in the commercials "wait...there's more!"
Once the user enters data in the "old Row 79, now Row 80", and a new Row 81 is inserted and Row 119 (which used to be 118) now becomes Row 120, and so on and so on.
As you said "Oh dear, this is getting complicated!"
What this means is that we can't use Row numbers to decide where to insert Rows, we need to use existing data in the Row where the data was entered to trigger the insertion. Is there something unique in each of the Rows where data is entered that the code could check to decide whether a new row needs to be inserted?
In English, not VBA code, I'm looking for something like this:
"If user data is entered in Column B or Column K and Column A of that same row equals (insert unique data here) then insert a row below the current row."
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.