How to Fix VBA Macro Failing to Copy Worksheets to a New Excel File
Question details
The user is trying to execute a VBA macro to copy specific worksheets and save them as a new XLSX file, but the code fails or encounters errors during execution.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Running a VBA script to automatically export selected worksheets and save them as a separate Excel workbook.
- Observed behavior
- The macro fails to execute properly, which may be caused by workbook context errors, missing sheet names, file permission limits, or SaveAs conflicts with existing files.
Before troubleshooting your VBA code, verify that the worksheets you are trying to copy actually exist in the source workbook (e.g., Sheet1, Sheet2, Sheet4) and that you have write permissions for the destination folder.
Update VBA Code with Timestamps and Workbook Context
Modifying the macro to explicitly reference the source workbook and adding a timestamp to the output filename prevents common saving and context errors.
When a macro copies sheets, it creates a new active workbook. If your code does not explicitly state where the original sheets are located using 'ThisWorkbook', Excel may look for them in the newly created blank workbook and fail. Additionally, saving a file with a static name repeatedly will cause a conflict if the file already exists.
Press ALT + F11 in Excel to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer on the left, find and double-click the module containing your worksheet copying macro.
Change your sheet references to use 'ThisWorkbook.Sheets' instead of just 'Sheets'. For example: ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet4")).Copy
Modify the .SaveAs Filename parameter to include a dynamic timestamp format. For example: .SaveAs Filename:="D:\myVBA_" & Format(Now, "ddmmyyyy-hhmmss") & ".xlsx"
Ensure you include FileFormat:=xlOpenXMLWorkbook in your SaveAs method to properly save it as an XLSX file.
Test the Macro in a Fresh Workbook
Sometimes a workbook's internal VBA project can become corrupted. Transferring the code to a brand new file can easily resolve unexplained runtime errors.
Run and Edit VBA Macros Seamlessly in WPS Spreadsheet
WPS Office provides robust support for Excel macros and VBA scripts. You can easily edit, troubleshoot, and run your VBA codes to copy worksheets just like you would in Microsoft Excel.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your macro-enabled workbook (.xlsm).
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu.
- 3. Open the VBA Editor: Click on 'Visual Basic' or press ALT + F11 to open the built-in VBA editor.
- 4. Run the Macro: Paste your worksheet copying code into a module and click 'Run' or press F5 to execute it.
Frequently Asked Questions
Why does my VBA SaveAs method throw a 1004 runtime error?
A runtime error 1004 during a SaveAs operation usually occurs if the file path is incorrect, the destination folder is read-only, or a file with the exact same name is already open or exists and cannot be overwritten.
How do I copy multiple worksheets into a new workbook using VBA?
You can use an array with the .Copy method, such as ThisWorkbook.Sheets(Array("Sheet1", "Sheet2")).Copy. When the Copy method is used without a destination parameter, it automatically creates a new active workbook containing only those copied sheets.
What is the difference between ActiveWorkbook and ThisWorkbook in VBA?
'ThisWorkbook' refers to the file where the VBA code is currently written and running. 'ActiveWorkbook' refers to the file that currently has focus in Excel, which changes dynamically when a new workbook is created via a macro.
Do I need to save the new workbook as a macro-enabled file (.xlsm)?
If the new workbook only contains the copied worksheets and does not need to contain any VBA code itself, you can safely save it as a standard Excel file (.xlsx) by using FileFormat:=xlOpenXMLWorkbook in your SaveAs code.
