Forum Discussion
Carrying down a formula when inserting a row on a protected sheet
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? HansVogelaar
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 SubBefore 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.
- 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.
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- 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.
