VBA & Macro Problems

Copy Data from a Closed Excel Workbook with VBA

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Copy values from a source workbook into a protected destination worksheet using a daily VBA macro.

Copy Data from a Closed Excel Workbook with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro needs to read cells A3:B203 from a fixed source file and update the destination without leaving the source workbook open.
Observed behavior
The desired result is for the destination columns to remain protected against manual edits while the macro updates their values.
Before you start

Find the source workbook's full path and both worksheet names. Save the destination as a macro-enabled workbook (.xlsm), and ensure the source workbook is closed before running the example.

Solution 1Recommended

Open the source workbook temporarily and update the protected sheet

Use a fixed file path to copy values without a file-picker dialog, then close the source and restore worksheet protection.

The macro opens the source workbook read-only; it does not read directly from a closed file. It copies A3:B203 to A4:B204 in the workbook containing the macro.

The example restores password protection if the destination sheet was protected before the update. Reapplying protection may reset custom protection permissions, so add any required Protect options to the code.

1
Add a VBA module

In the destination workbook, press Alt+F11, select Insert > Module, and paste the following code: Sub UpdateFromSource() Const SourcePath As String = "C:\Reports\Source.xlsx" Const SourceSheet As String = "Sheet1" Const DestinationSheet As String = "Sheet1" Const SheetPassword As String = "your-password" Dim src As Workbook Dim dst As Worksheet Dim wasProtected As Boolean Dim errorText As String On Error GoTo HandleError Set dst = ThisWorkbook.Worksheets(DestinationSheet) wasProtected = dst.ProtectContents If wasProtected Then dst.Unprotect Password:=SheetPassword Set src = Workbooks.Open(Filename:=SourcePath, UpdateLinks:=0, ReadOnly:=True) dst.Range("A4:B204").Value = src.Worksheets(SourceSheet).Range("A3:B203").Value GoTo CleanUp HandleError: errorText = Err.Description CleanUp: On Error Resume Next If Not src Is Nothing Then src.Close SaveChanges:=False If wasProtected Then dst.Protect Password:=SheetPassword If Err.Number <> 0 Then errorText = errorText & vbCrLf & "Could not restore sheet protection: " & Err.Description End If End If On Error GoTo 0 If Len(errorText) > 0 Then MsgBox errorText, vbExclamation, "Update incomplete" End Sub

2
Set the path and sheet names

In the module, replace SourcePath, SourceSheet, DestinationSheet, and SheetPassword with your actual values. If the source or destination range changes, make sure both ranges have the same number of rows and columns.

3
Check destination protection

On the destination sheet, select A4:B204 and open Format Cells > Protection to confirm the cells are locked. Use Review > Protect Sheet to protect them, and use the same password in the macro.

4
Run and verify the update

Close the source workbook, then in the destination workbook choose Developer > Macros, select UpdateFromSource, and click Run. Check A4:B204, confirm the source is closed, and verify that the destination cells are protected.

Open the source workbook temporarily and update the protected sheet
Enable macros only for trusted files: Excel may block macros from untrusted files. Enable the macro only if you trust the workbook and its source.
Free Microsoft Office alternative

Manage Excel spreadsheets with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility, making it easy to move everyday spreadsheet work. It does not fix Excel VBA problems; macro support can vary by WPS edition and configuration.

Open and edit Excel-compatible .xlsx spreadsheetsCopy and paste worksheet values through a familiar interfaceMove everyday spreadsheet work from Microsoft Office with compatible file formatsUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does this macro read the source workbook without opening it?

No. Workbooks.Open opens the source read-only, and the cleanup section closes it without saving. The source should be closed before you run the example.

Where are the copied values pasted?

The code copies A3:B203 from the source sheet to A4:B204 on the destination sheet. Change both ranges in the module if needed, keeping their dimensions equal.

Why can users still edit the destination cells?

Select A4:B204 and check Format Cells > Protection to make sure Locked is selected. Then use Review > Protect Sheet; locking cells alone does not prevent edits.

What if the macro cannot unprotect the sheet?

Check that SheetPassword in the VBA module matches the destination sheet's protection password. Also confirm that DestinationSheet names the correct worksheet.

Can I run this macro in any WPS Office installation?

VBA support varies by WPS edition and configuration. The example is intended for Microsoft Excel; check your WPS installation's macro support before relying on it.