How to Fix Excel Macros Opening the Original Workbook Instead of a Renamed Copy
Question details
Excel VBA macros inadvertently open or modify the original workbook after the file has been renamed or copied.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running VBA macros in a copied or renamed workbook to process daily data with variable row counts.
- Observed behavior
- The macro continues to target or open the original workbook instead of executing within the newly renamed copy.
Before modifying your VBA code, create a safe backup of your current workbook and ensure the Developer tab is enabled in your ribbon so you can access the Visual Basic Editor.
Fix Hard-Coded Workbook Names and Object References
Update your VBA code to reference the workbook running the code rather than a hard-coded file name.
Macros often fail in copied workbooks because the code explicitly calls out the original file's name. Replacing these with dynamic references ensures the macro always applies to the file it is running from.
Press ALT + F11 to open the VBA editor and double-click the module containing your macro in the Project Explorer.
Press CTRL + F to search for references like Workbooks("OriginalName.xlsx") or similar hard-coded file names in your code.
Change the hard-coded workbook references to ThisWorkbook. This tells VBA to execute the code on the file where the macro is stored, regardless of its current file name.
Replace .Select and ActiveCell methods with direct object references, such as ThisWorkbook.Sheets("Sheet1").Range("A1"), to prevent the macro from jumping to unintended open workbooks.
Store Reusable Macros in Personal.xlsb
If a macro needs to run across multiple different workbooks, save it in the Personal Macro Workbook so it is not tied to a specific file.
Run and Edit VBA Macros Smoothly with WPS Spreadsheet
WPS Office provides excellent support for VBA macros, allowing you to run, edit, and debug your existing Excel scripts without modification. Enjoy a lightweight, fast, and highly compatible spreadsheet environment.
- 1. Open Your Macro Workbook in WPS: Launch WPS Spreadsheet and open your .xlsm or .xlsb file containing the macros.
- 2. Enable Macros: Click 'Enable Macros' in the security warning prompt that appears at the top of your workspace.
- 3. Access the VBA Editor: Navigate to the Developer tab and click on the 'VBA Editor' icon to review or modify your macro code.
Frequently Asked Questions
Why does my macro work on the original file but not the renamed copy?
This happens because the VBA code explicitly names the original file (e.g., Workbooks("Original.xlsx")). When you run it from the copy, VBA looks for the exact name of the original file instead of applying the actions to the copy.
What is the difference between ActiveWorkbook and ThisWorkbook in VBA?
ActiveWorkbook refers to the file currently in focus on your screen, which can change unexpectedly if another file opens. ThisWorkbook specifically refers to the file where the VBA code itself is stored, making it much more reliable for referencing.
How do I make a macro automatically handle different numbers of rows each day?
Instead of hard-coding row numbers or using .Select, use dynamic range calculations in your VBA code. For example, Cells(Rows.Count, 1).End(xlUp).Row will find the last used row dynamically.
Where is the Personal.xlsb file stored on my computer?
In Windows, the Personal Macro Workbook is typically stored in the XLSTART folder located at C:\Users\[Username]\AppData\Roaming\Microsoft\Excel\XLSTART. It opens invisibly in the background whenever you start the application.
