How to Fix Excel VBA Error 1004 When Copying to Protected Sheets
Question details
The user wants to copy data into protected worksheets using VBA without triggering runtime Error 1004.

- 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 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.
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.
Review your protected destination sheet to determine exactly which columns are unlocked and available for data entry.
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.
Use the Copy method to paste these specific ranges into the corresponding unlocked destination cells, such as `SourceRow1.Copy Destination:=Destination1`.

Protect the Worksheet with UserInterfaceOnly Set to True
Allow your VBA macros to modify locked cells while still preventing users from editing them manually.
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.

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.




