Insert new row and copy formula

Question

I have this table in Excel:

and I have the following formula in column N:

Under the button named Adauga, I have a subroutine that adds new rows before row 5:

Sub Button3_Click()

    Sheets("Sheet1").Range("A5").Select
    ActiveCell.EntireRow.Insert Shift:=xlDown

End Sub

But the formula does not copy into the new row. How can I modify my code to copy the formula also? I tried some methods but I ended up copying the values also. It is important that the row inserts before row 5.

Bond Bond · Accepted Answer · 2015-07-25T13:03:21

Excel doesn't maintain the formulas being used unless you set up a table. So you'll just need to add an extra step to your process:

Sub Button3_Click()

    With Sheets("Sheet1")
        .Rows("5").Insert xlDown, xlFormatFromLeftOrAbove
        .Range("N5").FillDown
    End With

End Sub

This will insert a row at row 5 and then just copy the formula from above.

Insert new row and copy formula

1 Answers