VBA & Macro Problems

How to Copy Excel Macros and Buttons to a New Workbook Without Links

Nimra Malik Sep 28, 2026 869 views

Question details

The user needs to duplicate a worksheet along with its VBA modules and form control buttons into a new workbook, ensuring the buttons link to the new workbook's macros rather than the original file.

How to Copy Excel Macros and Buttons to a New Workbook Without Links

Product: Excel
Device & OS: not provided
Scenario: Copying an existing worksheet with macro-assigned buttons to a completely new Excel workbook.
Observed behavior: After copying the worksheet and VBA modules, the buttons' OnAction property still retains the original source workbook name, causing the old workbook to open or be referenced when the button is clicked.

Before you start

Ensure the Developer tab is enabled in your spreadsheet program and that you save the new destination file as a Macro-Enabled Workbook (.xlsm) before testing any copied code.

Solution 1Recommended

Manually Update Button Macro Assignments in the New Workbook

Copy the required VBA modules to the new workbook, then manually reassign each button to the local macro to sever the link to the original file.

When copying worksheets, Excel automatically maintains absolute references to the original macros. To fix this, you must update the OnAction property of each button in the destination workbook.

Copy the VBA Modules

Open both the source and destination workbooks. Press Alt + F11 to open the VBA Editor, drag the required VBA modules from the source workbook's project tree to the destination workbook, and close the editor.

Save as Macro-Enabled Workbook

In the destination workbook, go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure the copied macros are preserved.

Assign Macro to the Button

Right-click the copied button in the new workbook and select 'Assign Macro' from the context menu.

Select the Local Macro

In the Assign Macro dialog, select the macro name that corresponds to the current (new) workbook. Ensure the macro path does not contain the name of the original file, then click OK.

Test the Button

Save and close the workbook. Reopen it independently to verify that clicking the button runs the local macro without opening the original source file.

Manually Update Button Macro Assignments in the New Workbook

Verification Step: Always test every button after reopening the new file to confirm that no external links are triggering the opening of the old workbook.

Manage Macros Easily with WPS Office

Use WPS Spreadsheet to Edit and Run Macros Seamlessly

WPS Office provides excellent support for VBA macros and form controls. You can effortlessly copy sheets, transfer VBA code, and assign macros exactly as you do in Microsoft Excel.

1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the macros and buttons.
2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon to access the VBA Editor and Macro manager.
3. Update Button Links: Right-click any form control button, choose 'Assign Macro', and select the local script to remove external file dependencies.
4. Save Your Work: Click the Save icon to securely store your macro-enabled spreadsheet.

Fully compatible with Microsoft Excel .xlsm and .xlsb macro formatsBuilt-in Developer tab for comprehensive VBA macro editing and debuggingFamiliar interface makes reassigning button macros quick and intuitiveLightweight application that runs smoothly even with complex VBA scripts

microsoft office alternative - wps office

Frequently Asked Questions

Why do copied Excel buttons still link to the old workbook?

When you copy a worksheet containing buttons assigned to macros, the application preserves the absolute file path to the original macro in the button's OnAction property. You must manually point it to the new local macro.

How do I quickly copy a VBA module to another workbook?

Open the Visual Basic Editor (Alt + F11), make sure the Project Explorer is visible, and simply click and drag the module from your source workbook's project into your destination workbook's project.

Can I automate the process of updating button macro links?

Yes. You can write a short VBA script in the new workbook that loops through all Shape objects (buttons) on the worksheet and modifies their OnAction property string to remove the external workbook name reference.

Excel & Data Problems