Copy Data from a Closed Excel Workbook with VBA
Question details
Copy values from a source workbook into a protected destination worksheet using a daily VBA macro.

- 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.
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.
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.
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
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.
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.
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.

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.

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.




