Forum Discussion

5 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Oct 21, 2023

    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
      Oct 24, 2023
      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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 24, 2023

        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