VBA & Macro Problems

Excel VBA: Fix Copy and Paste Between Macro-Enabled Workbooks

Olivia MillerOlivia Miller Nov 12, 2026 870 views

Question details

The user needs to restore cell copy and paste between two Excel macro-enabled workbooks while macros remain enabled.

Product
Microsoft Excel XLSM workbooks and Visual Basic for Applications
Device & OS
not provided
Scenario
Copying cells between two macro-enabled workbooks fails when macros are enabled, but works after macros are disabled.
Observed behavior
Workbook event procedures may switch the active window or change ActiveWindow settings during workbook activation and deactivation, interrupting cross-workbook copy and paste.
Before you start

Save a backup copy of each affected XLSM workbook before editing VBA code. Reproduce the issue with both workbooks open, then note which workbook becomes active when you switch between them.

Solution 1Recommended

Disable workbook event code that changes the active window

Use this solution when copying works with macros disabled and the affected workbook contains Workbook_Activate or Workbook_Deactivate procedures.

Workbook-level event procedures run automatically when a workbook is activated or deactivated. Code that changes ActiveWindow properties can interfere with Excel's cross-workbook copy state, so temporarily commenting out the event code helps confirm and resolve the cause.

1
Open the VBA editor

Open the affected XLSM workbook in Excel, then press Alt+F11 to open the Microsoft Visual Basic for Applications editor.

2
Open ThisWorkbook

In the Project Explorer pane, expand the affected workbook's VBA project and double-click ThisWorkbook.

3
Review workbook events

Look for procedures named Workbook_Activate or Workbook_Deactivate, especially code that changes ActiveWindow settings such as DisplayZeros.

4
Comment out the event code

Place an apostrophe at the beginning of each affected VBA line, or temporarily remove the event procedures. For example, comment out ActiveWindow.DisplayZeros = False and ActiveWindow.DisplayZeros = True.

5
Save and test copying

Close or switch back to Excel, save the workbook, keep macros enabled, and test copying cells between the two XLSM workbooks again.

Restore the code carefully: If copying works after the event procedures are disabled, reintroduce the code one procedure at a time to identify the exact line causing the conflict. Keep the backup available before making permanent VBA changes.
Free Microsoft Office alternative

Use WPS Office as a Lightweight Alternative for Excel Workbooks

WPS Office provides a familiar spreadsheet interface for opening, editing, and saving common Excel files. It is free, lightweight, supports Microsoft Excel formats such as XLSX and XLSM for compatible content, and can make migration from Microsoft Office straightforward; advanced VBA behavior may require Microsoft Excel for full macro compatibility.

Free and lightweight spreadsheet editor for everyday workbook tasksFamiliar interface that supports a smooth transition from Microsoft ExcelCompatible with Microsoft Excel XLSX and XLSM file formats for supported workbook contentUseful for reviewing and editing spreadsheets when Microsoft Office is unavailable
microsoft office alternative - wps office

Frequently Asked Questions

Why does copying work when Excel macros are disabled?

Disabling macros prevents Workbook_Activate and Workbook_Deactivate procedures from running. If those procedures modify ActiveWindow settings or change the active workbook, they can interrupt Excel's cross-workbook copy and paste behavior.

Where can I find the VBA code that runs when a workbook opens or becomes active?

In Excel, press Alt+F11, expand the workbook in Project Explorer, and double-click ThisWorkbook. Check for Workbook_Activate and Workbook_Deactivate event procedures.

What VBA code should I check first?

Check code that references ActiveWindow, including lines such as ActiveWindow.DisplayZeros = False or ActiveWindow.DisplayZeros = True. These settings can change the active window while Excel is transferring copied cells.

Should I delete the Workbook_Activate and Workbook_Deactivate procedures permanently?

Not necessarily. First comment out the procedures and test copying. If they are confirmed as the cause, revise the code or run it through a safer mechanism instead of permanently deleting functionality you still need.

Can WPS Office fix an Excel VBA event conflict?

WPS Office can open and edit compatible Excel workbooks, but it does not directly repair Microsoft Excel VBA event conflicts. Use Excel's Visual Basic Editor to troubleshoot advanced VBA behavior, and use WPS Office as an alternative for compatible spreadsheet editing.