Fix Excel Runtime Error 1004: Method Add of Sheets Failed
Question details
The user needs to resolve an issue where a macro fails to add a new worksheet in an older Excel workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to add a new worksheet in a legacy workbook originally created in 2005.
- Observed behavior
- The macro triggers 'Runtime Error 1004: Method Add of Sheets Failed' in the older workbook, despite the exact same macro running successfully in a newer, identical workbook.
Ensure you have the Developer tab enabled in your Excel ribbon to access the VBA editor, and verify whether your workbook structure is protected with a password.
Update Obsolete Macro Functions to VBA 7
Replace outdated Excel 4.0 macro functions with modern VBA code to restore full compatibility.
Older workbooks created in earlier versions of Excel (such as Excel 2003 or 2005) often rely on legacy Excel 4.0 macro functions. Microsoft has significantly reduced support for these obsolete functions in modern updates.
When modern Excel attempts to execute an outdated sheet-adding function, it triggers Runtime Error 1004. Upgrading the specific code block to standard VBA 7 alternatives will resolve this compatibility issue.
Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications (VBA) editor.
In the left-hand Project Explorer pane, find the module containing the macro that adds worksheets and double-click to open it.
Identify any obsolete Excel 4.0 macro commands and replace them with standard VBA sheet creation code, such as 'Sheets.Add After:=ActiveSheet'.
Click the Save icon, close the VBA editor, and run your macro again to confirm the worksheet is added without errors.
Check and Disable Workbook Protection
Verify that the workbook structure is not locked, which prevents adding, moving, or deleting sheets.
Edit Legacy Spreadsheets Smoothly with WPS Office
If you frequently encounter macro compatibility issues, runtime errors, or sluggish performance with legacy Microsoft Excel files, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative for managing your spreadsheets.
- 1. Download the software: Visit the official WPS Office website and click on 'Download WPS Office Free'.
- 2. Install WPS Office: Run the downloaded installer file and follow the on-screen prompts to set up the software.
- 3. Open your files: Right-click your existing Excel workbooks and select 'Open with WPS Spreadsheets' to start working immediately.
Frequently Asked Questions
Why does Error 1004 only happen in the older version of my workbook?
The older workbook (created around 2005) likely contains legacy configurations, Excel 4.0 macro structures, or specific file limitations (.xls format limits sheets and memory differently) that conflict with modern Excel's security and VBA execution engines.
How do I know if my workbook uses obsolete Excel 4.0 macros?
Legacy Excel 4.0 macros are often stored on hidden 'Macro Sheets' rather than in the modern VBA editor. Right-click on any visible sheet tab, select 'Unhide', and look for any sheets categorized as Macro sheets.
Can reaching the maximum worksheet limit cause Runtime Error 1004?
Yes. While modern Excel limits sheets only by available memory, older .xls formats or specific memory constraints can restrict adding new sheets. If the memory allocation is maxed out, the 'Add' method will fail and trigger this runtime error.
