How to Fix VBA Error 1004 When Copying Worksheets with Tables
Question details
The user needs to fix Run-time error 1004 that occurs when using a VBA macro to copy multiple worksheets containing tables.
- Product
- Spreadsheet/Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to copy a group of worksheets that contain formatted tables or are connected to SQL data.
- Observed behavior
- VBA raises Run-time error 1004, preventing the group of worksheets from being copied.
Before modifying your VBA script, ensure you have saved a backup copy of your macro-enabled workbook in case you need to revert your code.
Copy Worksheets Individually via VBA Loop
Avoid the error by modifying your VBA code to iterate through and copy each worksheet one by one instead of copying the entire group simultaneously.
Run-time error 1004 often occurs due to memory limitations or object referencing issues when the spreadsheet application attempts to duplicate multiple ListObjects (tables) or SQL data connections at exactly the same time. Using a VBA loop to iterate through the sheets individually safely bypasses this limitation.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor in your active workbook.
In your macro module, initialize a new workbook object to serve as the destination for the copied sheets. You can do this by defining a variable like `Set newWb = Workbooks.Add`.
Use a `For Each` loop to iterate through the specific worksheets in the source workbook. Inside the loop, copy them one at a time to the destination workbook using `ws.Copy After:=newWb.Sheets(newWb.Sheets.Count)`.
Add a few lines of code to target and delete the default blank worksheets (such as 'Sheet1') that were automatically generated when the new destination workbook was created.
Finish the macro by using the `SaveAs` method on your `newWb` object to save the newly created workbook containing all your successfully copied tables.
Use WPS Spreadsheet to Run and Edit VBA Macros
WPS Spreadsheet provides excellent compatibility with advanced macros, allowing you to edit VBA code, automate repetitive tasks, and manage complex worksheets with tables seamlessly.
- 1. Download and Install WPS Office: Get the latest version of WPS Office from the official website and install it on your system.
- 2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsb workbook containing the tables.
- 3. Access the Developer Tab: Navigate to the Developer tab on the ribbon to open the VBA Editor and view your modules.
- 4. Run Your Modified Script: Execute your updated VBA script to copy the worksheets individually without encountering Error 1004.
Frequently Asked Questions
Why does Error 1004 happen when copying grouped sheets with tables?
This error typically occurs because the application struggles to simultaneously duplicate complex objects like ListObjects (tables) or external SQL data connections across multiple sheets at the exact same time, triggering a run-time failure.
Can I copy multiple sheets manually without getting this VBA error?
Yes, manually selecting multiple worksheet tabs and right-clicking to copy them usually works fine. The Run-time error 1004 in this context is specifically a programmatic limitation when VBA attempts to execute the batch duplication of tables.
How do I suppress the deletion warning when removing default sheets in my macro?
You can suppress the warning by adding `Application.DisplayAlerts = False` immediately before your `Sheet.Delete` command, and then restoring it by adding `Application.DisplayAlerts = True` right after the deletion line.
