Excel VBA: Copy a Formula to a New Row on a Protected Sheet
Question details
The user needs a VBA macro that inserts a new row, copies the preceding row's formula from column E, and restores worksheet protection.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A worksheet is protected, but a new row must be added while preserving the formula pattern from the previous row.
- Observed behavior
- The protected worksheet prevents the macro from inserting a row or writing the formula unless protection is temporarily removed.
Save a backup copy of the workbook, then confirm the worksheet name, protection password, and formula column. Make sure macros are enabled and that the last populated formula row is in column E.
Use VBA to insert the row, copy the formula, and restore protection
Use this macro when the worksheet is protected and the new row should inherit the preceding formula in column E.
The macro temporarily removes protection, identifies the last used row in column E, inserts the next row, and assigns the preceding formula using FormulaR1C1 so relative references adjust correctly. An error handler protects the worksheet again if the operation fails.
In Excel, open the workbook and select Developer > Visual Basic, or press Alt+F11. Choose Insert > Module to create a standard code module.
Paste this code into the module, then replace Sheet1, yourPassword, and column E if your workbook uses different settings: Sub InsertRowAndCopyFormula() Dim ws As Worksheet Dim lastRow As Long Dim pwd As String On Error GoTo HandleError Set ws = ThisWorkbook.Worksheets("Sheet1") pwd = "yourPassword" ws.Unprotect Password:=pwd lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row ws.Rows(lastRow + 1).Insert Shift:=xlDown ws.Cells(lastRow + 1, "E").FormulaR1C1 = ws.Cells(lastRow, "E").FormulaR1C1 ws.Protect Password:=pwd MsgBox "The new row was inserted and the formula was copied.", vbInformation Exit Sub HandleError: On Error Resume Next ws.Protect Password:=pwd On Error GoTo 0 MsgBox "The operation could not be completed: " & Err.Description, vbExclamation End Sub
Return to the workbook, select Developer > Macros, choose InsertRowAndCopyFormula, and click Run. Confirm that a new row appears after the last populated row, column E contains the adjusted formula, and the worksheet is protected again.
Automate protected-sheet formula copying with WPS Spreadsheet
WPS Spreadsheet can run a similar VBA macro workflow: open the macro editor, temporarily unprotect the sheet, insert a row, copy the formula pattern, and protect the sheet again. Its familiar spreadsheet interface supports a practical transition from Excel workbooks.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the protected worksheet.
- 2. Open the macro editor: Use the Tools or Developer menu to open the Visual Basic or Macro editor, then create a standard module for the row-insertion macro.
- 3. Customize and run the macro: Set the worksheet name, protection password, and formula column in the macro, then run it from the Macro dialog to insert the row and copy the formula.
- 4. Verify the protected result: Check that the new row contains the adjusted formula and confirm that worksheet protection is enabled after the macro finishes.

Frequently Asked Questions
Why must the macro unprotect the worksheet before inserting a row?
A protected worksheet usually blocks structural changes such as inserting rows. Call ws.Unprotect Password:=pwd before the insert operation, then call ws.Protect Password:=pwd after the formula is copied.
How do I change the formula column from E to another column?
Replace every "E" used to find the last row and assign the formula. For example, use "F" in ws.Cells(ws.Rows.Count, "F") and in the FormulaR1C1 assignment if the formula is stored in column F.
What if the worksheet has no password?
Set pwd to an empty string and use ws.Unprotect Password:=pwd and ws.Protect Password:=pwd, or omit the Password argument if the sheet was protected without a password.
Why use FormulaR1C1 instead of copying the entire cell?
FormulaR1C1 transfers only the formula and adjusts relative references for the new row. It avoids copying formatting, comments, or other cell content along with the formula.
How can I make sure the sheet is protected if the macro fails?
Use an error handler that runs ws.Protect Password:=pwd before displaying the error. Test the macro on a backup copy and verify protection after both successful and failed runs.




