I am using the following code to insert and delete rows on a protected worksheet when there are locked columns. I want to be able to copy down any formulas from the row above when inserting new rows, but literally only formula not the content of the entire row. On the attached sample when inserting one or multiple rows, I would want to insert blank rows whilst copying the formula in columns N and AA down so the user can make new entries and the rows still sum. Is this possible? This would relate to any and all formula.
Using
ActiveCell.EntireRow.Copy
copies the entire row.
This code on ThisWorkbook
'Change the password "pass" to your password (include the apostrophes)
Const PW = "pass"
Private Sub Workbook_Open()
For Each sht In ActiveWorkbook.Sheets
sht.Protect _
Password:=PW, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
Next sht
End Sub
And this code in a module
Sub InsertRows()
Dim J, r As Range
J = Application.InputBox("Type the Number of Rows to be Inserted", , , , , , , 1)
If J = False Then Exit Sub
Set r = Selection
Range(r.Offset(1, 0), r.Offset(J, 0)).EntireRow.Insert
End Sub
Sub DeleteRow()
' Delete a row
Selection.EntireRow.Delete Shift:=xlUp
End Sub
Many thanks
Bookmarks