VBA & Macro Problems

How to Add Rows to a Protected Excel Sheet While Preserving Formulas

Bushra Parveen Oct 16, 2026 868 views

Question details

The user wants to automate the process of inserting new rows into a protected spreadsheet so that formulas are preserved while keeping locked cells safe from unwanted edits.

How to Add Rows to a Protected Excel Sheet While Preserving Formulas

Product: Excel
Device & OS: not provided
Scenario: Creating a user-friendly system for less experienced users to add new rows with existing formulas without breaking or manually adjusting the worksheet protection.
Observed behavior: Inserting rows natively on a protected sheet often restricts formula copying or is too manual; the goal is a seamless, automated row insertion process.

Before you start

Ensure the Developer tab is enabled in your spreadsheet ribbon, as you will need it to record or write the macro for this automation.

Solution 1Recommended

Create a Macro to Automate Row Insertion and Formula Copying

Record a macro that automatically inserts a new row and copies the formulas from the previous row, which can then be assigned to a button for easy access.

By recording a macro, you can combine the steps of adding a row, copying formula cells, and maintaining cell locks into a single action. This saves time and ensures consistency for less experienced users.

Unlock Editable Cells

Select the cells that users should be allowed to modify. Right-click, choose 'Format Cells', open the 'Protection' tab, and clear the 'Locked' checkbox.

Start Recording the Macro

Go to the 'Developer' tab on the ribbon and select 'Record Macro'. Enter a memorable name for the macro, choose where to store it, assign an optional shortcut key, and select 'OK'.

Insert the New Row

Right-click the row header below your desired insertion point and choose 'Insert' to add a blank row.

Copy the Formulas

Select only the formula cells in the previous row and press Ctrl+C. Then, select the corresponding cells in your newly inserted row and press Ctrl+V to paste the formulas.

Stop Recording

Return to the 'Developer' tab and click 'Stop Recording'. You can now use your assigned shortcut or link this macro to a button to run the entire sequence instantly.

Create a Macro to Automate Row Insertion and Formula Copying

Assign to a Button: To make this even more user-friendly, insert a Form Control button from the Developer tab and assign this newly recorded macro to it. Users can simply click the button to add a formatted row.

Solution 2

Manually Add a Row and Adjust Protection Settings

If you only need to perform this action occasionally, you can manually insert the row, adjust cell locking settings, and re-apply worksheet protection.

Automate Workflows Easily

Manage Macros and Protected Sheets in WPS Spreadsheets

WPS Spreadsheets provides robust support for worksheet protection and macro automation. You can easily record sequences to handle repetitive tasks like inserting rows while preserving your complex formulas.

1. Open Your Workbook: Launch WPS Spreadsheets and open your data-heavy or protected workbook.
2. Use the Developer Tab: Navigate to the Developer tab to record a new macro or manage existing VBA scripts for row insertion.
3. Protect Your Work: Switch to the Review tab to easily apply, customize, or remove Sheet Protection.

Fully compatible with Microsoft Excel (.xlsx and .xlsm) macro-enabled formats.Advanced worksheet protection features to secure sensitive formulas.Intuitive built-in Developer tools for recording and executing macros effortlessly.Lightweight, fast, and completely free to use for daily office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I run a macro while an Excel sheet is currently protected?

By default, macros cannot modify locked cells in a protected sheet. You must either include code in your macro to unprotect and reprotect the sheet during execution, or apply sheet protection using the 'UserInterfaceOnly' parameter in VBA.

How do I assign my newly recorded macro to a clickable button?

Go to the 'Developer' tab, click 'Insert', and select the Button form control. Draw the button on your worksheet, and when the 'Assign Macro' dialog appears, select your row-inserting macro and click 'OK'.

Why is the 'Record Macro' button missing from my ribbon?

The Developer tab is often hidden by default. To display it, right-click anywhere on the ribbon, select 'Customize the Ribbon', check the box next to 'Developer' in the right-hand list, and click 'OK'.

Will copying the row also copy the data validation rules?

Yes, when you copy and paste the entire row or specific cells from the previous row, any existing data validation rules attached to those cells will be carried over to the newly inserted row.

Excel & Data Problems