VBA & Macro Problems

How to Fix Excel VBA Error 1004 When Copying to Protected Sheets

Khadija KhanKhadija Khan Nov 11, 2026 868 views

Question details

The user wants to copy data into protected worksheets using VBA without triggering runtime Error 1004.

How to Fix Excel VBA Error 1004 When Copying to Protected Sheets
Product
Excel
Device & OS
not provided
Scenario
Running a VBA macro to transfer data from one workbook to a protected worksheet in another workbook.
Observed behavior
A runtime Error 1004 occurs because the macro attempts to paste data into locked cells on the protected destination worksheet.
Before you start

Before modifying your VBA code, ensure you have the password for the protected worksheet and clearly identify which specific columns or cells are unlocked for data entry.

Solution 1Recommended

Split the Copy Range to Target Only Unlocked Columns

Prevent Error 1004 by copying specific data ranges that correspond exactly to the unlocked cells in your destination sheet, instead of copying entire rows.

When you attempt to paste an entire row into a protected worksheet, Excel rejects the operation if any cell in the destination row is locked. To resolve this, you must split your source data into smaller ranges that map directly to the unlocked columns in the target sheet.

1
Identify locked and unlocked columns

Review your protected destination sheet to determine exactly which columns are unlocked and available for data entry.

2
Split your source ranges in VBA

Update your macro code to define smaller source ranges using offset and resize commands. For example, use `Set SourceRow1 = ProductRef.Offset(0, 3).Resize(1, 4)` to grab specific columns rather than the entire row.

3
Copy and paste into unlocked destinations

Use the Copy method to paste these specific ranges into the corresponding unlocked destination cells, such as `SourceRow1.Copy Destination:=Destination1`.

Split the Copy Range to Target Only Unlocked Columns
Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet and Macro Needs

If you frequently work with macros, data transfers, and protected sheets, consider switching to WPS Office. It is a lightweight, highly compatible alternative to Microsoft Office that offers excellent support for VBA macros and seamless document management.

Free and lightweight Microsoft Office alternativeHigh compatibility with Microsoft Excel (.xlsx, .xlsm, .csv) formatsRobust VBA macro support for automating complex spreadsheet tasksIntuitive user interface for managing worksheet protection effortlessly
microsoft office alternative - wps office

Frequently Asked Questions

What does VBA Error 1004 mean in Excel?

VBA Error 1004 is a generic runtime error in Excel that usually occurs when a macro tries to perform an invalid action, such as pasting data into a locked cell on a protected worksheet, referencing a range that doesn't exist, or exceeding system limits.

How do I temporarily unprotect a sheet in VBA before pasting data?

You can unprotect a sheet at the start of your macro using the code `Worksheets("Sheet1").Unprotect "password"`. Remember to re-apply the protection using `Worksheets("Sheet1").Protect "password"` immediately after your paste operations are complete.

Does WPS Office support running Excel VBA macros?

Yes, WPS Office supports VBA macros. You can open, edit, and run .xlsm files smoothly, making it a highly compatible alternative to Microsoft Excel for executing your automated scripts.