VBA to add row and copy formula from a cell and paste into the cell of the new row-CodePudding

I'm very new to VBA so don't understand a lot of things. I've 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. There is probably a better way of doing this.

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

My current VBA code to add the new row is:

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

Any help is appreciated

CodePudding user response：

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

CodePudding user response：

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.