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

You can see this in the attached sheet. Relative Formula Copy and Insert.xlsm

Row 3 and Row 4 should be copied and inserted between rows 5 and 6, 6 and 7, 7 and 8, etc.

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


I'm trying to get the following macro to copy and insert 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.


Sub insertValues()
  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 > 6
          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!