Carrying down a formula when inserting a row on a protected sheet

[lobo114]

In my attached example, I am trying to carry down the formula in column E to a newly inserted row on a protected sheet. Anyone have any ideas on how to accomplish this? @Hans Vogelaar

[#2]

@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.

[#3]

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.

[#4]

@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

[#5]

That works, but is there a way to run this without assigning it to a command button or Quick Access Toolbar? I'm looking to have this macro run when you insert a row in the worksheet itself.

[#6]

@lobo114 

That'd be complicated, since there is no event that occurs specifically when a row is inserted.