Forum Discussion
lobo114
Oct 20, 2023Copper Contributor
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
5 Replies
Sort By
- NikolinoDEGold Contributor
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.
- lobo114Copper ContributorI'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