VBA & Macro Problems

How to Use a VBA Macro to Copy Data from a Protected Excel Worksheet

Chanuka Geekiyanage Oct 22, 2026 868 views

Question details

The user needs a VBA macro to successfully copy data from a protected Excel worksheet, run cleanup tasks, and paste it back without triggering VBA errors.

How to Use a VBA Macro to Copy Data from a Protected Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Running a cleanup macro on data stored in a protected worksheet by copying a specific range to a temporary workbook, and then copying the cleaned range back to the source.
Observed behavior: The protected status prevents the cleanup macros from running. Additionally, unqualified cell references in the VBA code trigger a Run-time error 1004 (application-defined or object-defined error) during the copying process.

Before you start

Ensure you have the password for the protected worksheet, as your VBA code will need to temporarily unprotect the sheet before executing the copy and paste commands.

Solution 1Recommended

Unprotect the Worksheet and Qualify Cell References

Temporarily unprotect the source worksheet at the start of your macro, fully qualify all range references to avoid Error 1004, and reprotect the sheet once the operation is complete.

When working with protected worksheets, any macro that modifies data or structure will fail unless the sheet is explicitly unprotected first. Referencing ranges without declaring the parent worksheet can also cause an application-defined error (Run-time error 1004) because VBA may attempt to resolve the reference against the wrong active sheet.

Unprotect the source sheet

Begin your macro by unprotecting the source worksheet using the command: Worksheets("SheetName").Unprotect "YourPassword".

Identify the last used row and column

Use VBA functions to find the last used row and column so you can define the target range dynamically.

Fully qualify your Range and Cells

Ensure every Range and Cells reference includes the parent worksheet. For example, use SourceSheet.Range("B6", SourceSheet.Cells(LastRow, LastColumn)) instead of an unqualified Cells reference.

Copy data to a temporary workbook

Execute the copy command to transfer the values or text from B6 to the last used cell into a newly created temporary workbook.

Clean and paste the data back

Run your cleanup macro on the temporary workbook's data. Once finished, copy the cleaned range and paste it back into B6 of the source worksheet.

Reprotect and close without saving

Reprotect the source sheet using Worksheets("SheetName").Protect "YourPassword". Finally, close the temporary workbook using ActiveWorkbook.Close SaveChanges:=False.

Unprotect the Worksheet and Qualify Cell References

Avoid Using Cut: Use the Copy and Paste methods instead of Cut. Cutting data can disrupt existing references and formulas, especially when the source data structure must remain intact.

Advanced VBA Support in WPS

Easily Manage VBA Macros with WPS Spreadsheet

WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to automate repetitive tasks, manage protected worksheets, and handle complex data operations smoothly.

1. Enable Developer Tools: Open WPS Spreadsheet, go to the Options menu, and ensure the Developer tab is enabled to access Macro features.
2. Open the VBA Editor: Click on the 'Developer' tab and select 'Visual Basic' or press ALT + F11 to launch the VBA Editor.
3. Insert and Run Code: Insert a new Module, paste your fully-qualified range and unprotect macro code, and run it to seamlessly process your data.

Full compatibility with Microsoft Excel VBA scripts and macrosSeamless handling of protected worksheets and dynamic rangesLightweight application with fast macro execution speedsFree and easy-to-use spreadsheet interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get Run-time error 1004 when copying a range in VBA?

Run-time error 1004 often occurs if your Cells or Range references are not fully qualified. If you write Range("A1", Cells(10,10)) instead of Sheet1.Range("A1", Sheet1.Cells(10,10)), VBA might reference the wrong active sheet, causing a structural conflict.

Can a VBA macro run on a protected worksheet without unprotecting it?

Generally, macros cannot modify locked cells on a protected worksheet. However, you can allow macros to bypass protection by applying the protect method with the argument UserInterfaceOnly:=True during the workbook open event.

How do I close a temporary workbook in VBA without saving?

You can close a workbook without prompting a save dialog by using the command ActiveWorkbook.Close SaveChanges:=False at the end of your macro execution.

Excel & Data Problems