Copy, Insert and maintain relative formula reference between rows...

[m-steele]

I am trying copy multiple rows of data and insert it between every existing row of data in my sheet.

Header Row
Row 2
Row 3 (Copy this)
Row 4 (Copy this)
< Insert row 3 and 4 with relative formulas
Row 5
< Insert row 3 and 4 with relative formulas
Row 6
< insert... etc...

Row 3 and Row 4 should be copied and inserted between rows 5 and 6, 6 and 7, 7 and 8, etc. The formula must maintain relative reference.

This must copy the cell formulas not the values. It must also copy the whole row not just the first column.

I have he following macro which almost works but the inserted formula is exactly as it was copied and I need it to be relative. As in change the formula based on where the formula is inserted.

Currently if the formula in the cell refers to L2 the formula copied also contain L2.

I need the formula to change the reference so it matches with the row above it. Another words if the cell is inserted 10 rows below where it was copied it needs to reference L12 instead of L2.

I think this is called relative reference.

Im not sure how to change this so that it maintains relative reference. Here is what i got so far.

Sub insertFormulas()
  Dim x
    Application.ScreenUpdating = 0
     x = Cells(3, 1).Resize(2, 50).Formula
      For i = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
      j = 1
        Do Until j > 2
          Cells(i, 1).EntireRow.Insert
          j = j + 1
        Loop
         Cells(i, 1).Resize(2, 50).Formula = x
      Next
    Application.ScreenUpdating = 1
End Sub

Thank you for any help!

[vbfbryce]

Step thru the attached code and see if it gets you close...insertRows.zip


NOTE: Excel 2010