using a macro to paste a relative reference formula

Question

I am building an expense report. I've got most of the cells locked down and the sheet is protected so users can only put data where I want it and they can't change formulas. I've got a block of five rows where they can enter expenses, but if they need more, I have a macro that will unprotect, insert a row, format the new row as needed, then re-protect. I've got that dow

Rows 6-10 are expense line items. Row 11 sums rows 6-10, let's say all of them in column D. I named cell A10 "domestic". I start the macro by going to "domestic" and inserting a row above it. The SUM formula is now in row 12, and it sums rows 6-11. But I have formating (merged cells) and other formulas (concatenates, vlookups) that I want copied from row 9 to the new row 10. And if the user needs another row, I want the macro to insert and format the new row directly above "domestic" again, so that the formula in column D is now on row 13 and it will sum rows 6-12. And so forth.

What this all boils down to is that I just don't know how to say "go to domestic, copy the entire row above it, go back to domestic, insert a row above domestic paste the clipboard to the new row". How do I make the copy and paste commands relative to the active cell, rather than specific to the cells I clicked on when I first recorded the macro?

Answer 1

This should do it:

Sub InsertRow()

Dim TargetRng As Range

Set TargetRng = Range("Domestic")

TargetRng.Offset.EntireRow.Insert

TargetRng.Offset(-2, 0).EntireRow.Copy

With TargetRng.Offset(-1, 0)

    .PasteSpecial xlPasteFormats

    .PasteSpecial xlPasteFormulas

End With

Application.CutCopyMode = False

End Sub