VBA & Macro Problems

Excel VBA: Copy a Formula to a New Row on a Protected Sheet

Adam DavisAdam Davis Nov 12, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic, or press Alt+F11. Choose Insert > Module to create a standard code module.

2
Add the protected-sheet macro

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

3
Run and verify the macro

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.

Protection is restored after an error: The error handler attempts to protect the worksheet again before displaying the error message. Test the macro on a copy first, especially when the workbook uses additional protection settings.

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. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the Excel workbook containing the protected worksheet.
  2. 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. 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. 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.
Automate inserting rows and copying formulas on protected worksheetsUse a familiar spreadsheet interface with macro tools for repeatable tasksOpen and edit Microsoft Excel XLSX workbooks with strong format compatibilityPreserve formula structures and streamline migration from Excel
microsoft office alternative - wps office

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.