Copy paste first row with correct cell reference

When I use this VBA code, I copy the first row (with many formulas, like one in B1 that says = A1*2) and add rows where my marker is. In cell A2 I specify how many rows I want to be added.

Code:

---

Sub addnewrows()

With ActiveCell.Resize([A2]).EntireRow
  .Insert
  .Offset(-[A2]).Value = Rows(1).FormulaR1C1
End With

End Sub

---

If A2 equals 3, the macro adds 3 rows.
If my mouse is on row 10, then new rows will be added to 10, 11 and 12.

Problem is that cell reference in row 11 and 12 is not correct.

Row 10 has formula A10 * 2 (correct)
Row 11 has formula A12 * 2 (not correct)
Row 12 has formula A14 * 2 (not correct)

I would like the formula number to equal row number

Hello jokris,

If you rewrite your macro this way...

Code:

---

Sub addnewrows()
   
    Dim Rng As Range
   
        Set Rng = ActiveCell.EntireRow
        Set Rng = Rng.Resize(RowSize:=Range("A2"))
       
        Rng.Columns(2).FormulaR1C1 = "=RC[-1]*2"
   
End Sub

---

It will work.

Sorry it did not work. My cell * 2 was just an example, the whole first row includes many cells with different formulas.

When trying the code with three rows inserted into row 10, it replaced the value of cell B10, B11 and B12 but it didn't insert new rows.
I would like all columns with all codes and formatting in my first row to be copied and inserted into new rows.