Forum Discussion

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    lobo114 

    I could imagine something like that with VBA, I haven't opened your file (for own security reasons) but I think it could work like that.

    Sub InsertRowAndCopyFormulas()
        Dim ws As Worksheet
        Dim LastRow As Long
        
        ' Set the worksheet you're working with
        Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
        
        ' Unprotect the sheet to make changes
        ws.Unprotect
        
        ' Find the last row with data in column E
        LastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
        
        ' Insert a new row below the last row
        ws.Rows(LastRow + 1).Insert
        
        ' Copy the formula from the cell above the insertion point (E1 in this case)
        ws.Cells(LastRow, "E").Copy ws.Cells(LastRow + 1, "E")
        
        ' Protect the sheet again
        ws.Protect
    End Sub

    Before you try it, I recommend backing up the file.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

     

    • lobo114's avatar
      lobo114
      Copper Contributor
      I'm looking for when any row is selected and a new row is inserted that the formula will be copied down, not just when that last row is selected.
      • lobo114 

        Try this. You could assign it to a command button and/or a Quick Access Toolbar:

        Sub InsertRow()
            Dim ws As Worksheet
            Set ws = ActiveSheet
            ws.Unprotect
            Selection.EntireRow.Insert
            ws.Protect AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, DrawingObjects:=False
        End Sub

Resources