How to Fix Excel VBA Error 1004 When Renaming Worksheets
Question details
The user needs to resolve an Excel VBA Runtime Error 1004 that occurs when a macro attempts to assign a firm name to a new worksheet.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to create and rename separate workbooks/worksheets for different firms based on cell values.
- Observed behavior
- The macro stops and displays Error 1004 after processing the first firm because the assigned worksheet name exceeds the maximum character limit.
Before modifying your macro, check your source data to ensure the firm names do not contain special characters like asterisks (*) or brackets ([]), as these are also invalid for worksheet names.
Limit Worksheet Name Length using the VBA Left Function
Modify your VBA script to automatically truncate the assigned worksheet name to a maximum of 31 characters, which is the hard limit in Excel.
Excel imposes a strict 31-character limit on worksheet names. If a cell value used for naming exceeds this length (such as a 34-character firm name), VBA will throw Runtime Error 1004. By wrapping the naming logic in a `Left()` function, you can prevent this error from breaking your workflow.
Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window, then locate the module containing your workbook creation macro.
Find the specific line of code that assigns the worksheet name. It usually looks similar to `shR.Name = shT.Cells(i, 1)`.
Modify the code to extract only the first 31 characters of the cell value. Change the line to: `shR.Name = Left(shT.Cells(i, 1), 31)`.
Save your code changes, return to your workbook, and run the macro again to verify that it processes all firm names without triggering Error 1004.

Run Spreadsheets and Macros Smoothly with WPS Office
Looking for a reliable spreadsheet program? WPS Office offers a free, lightweight alternative to Microsoft Office with excellent compatibility for Excel files and robust macro support. Manage your workbooks and run VBA scripts efficiently without expensive subscription fees.

Frequently Asked Questions
What is the maximum length for an Excel worksheet name?
A worksheet name in Excel can contain a maximum of 31 characters. Attempting to exceed this limit manually or via VBA macros will immediately result in an error.
Which characters are invalid for worksheet names?
You cannot use the characters \, /, ?, *, :, [, or ] in a worksheet name. Attempting to include any of these characters through a VBA script will also cause Runtime Error 1004.
Why do I get Error 1004 when creating multiple workbooks?
Error 1004 in workbook creation usually means the macro is trying to assign an invalid worksheet name. This happens if the source text exceeds 31 characters, contains illegal characters, or tries to apply a name that already exists in the workbook.




