How to add row and copy formula from a cell and paste into a cell of the new row?

Question

I made a button that adds a new row above another row where the value of the cell in column C is "add row above".

I did it like this because there is a formula on the row below that which totals all of column E.
So when I add a row above C with value add row above it auto updates the formula.

I need to copy a formula from column B into each now. The formula is =ROW(A1) so it numbers the row.

My code to add the new row:

Sub AddRow()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If Cells(i, "C").Value = "Add row above" Then If i > 1 Then Rows(i).Resize(1).Insert xlUp
    Next
Application.ScreenUpdating = True
End Sub

Answer 1

Insert Row and Copy Formula

• Note that =ROW(A1), =ROW(Z1) or just =ROW() produces the same result.

Option Explicit

Sub AddRow()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
    For i = Lastrow To 2 Step -1
        If Cells(i, "C").Value = "Add row above" Then
            Rows(i).Insert xlShiftDown
            Cells(i + 1, "B").Copy Cells(i, "B")
            ' Or (if below is not numbered):
            'Cells(i - 1, "B").Copy Cells(i, "B")
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Answer 2

I find it easier to create a named range and refer to that. This way, if it moves around the sheet, the named range will follow it and you don't have to go looking.

When you do that, this code works quite easily, you just need to adapt it.

Also, the ROW() function doesn't actually need a parameter IF you want to refer to the row that the ROW() formula is on.

Public Sub AddRowAndCopyFormula()
    Dim lngAddAtRow As Long
    
    With ThisWorkbook.Names("AddRowAbove")
        lngAddAtRow = .RefersToRange.Cells(1, 1).Row
        .RefersToRange.Worksheet.Rows(lngAddAtRow).Insert xlShiftDown
        .RefersToRange.Worksheet.Range("B" & lngAddAtRow).Formula = "=ROW() - 7"
    End With
End Sub

This is what my worksheet looks like.