VBA & Macro Problems

How to Fix Excel VBA Error 1004 When Renaming Worksheets

Huda QurayshiHuda Qurayshi Oct 10, 2026 873 views

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.

How to Fix Excel VBA Error 1004 When Renaming Worksheets
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 you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications window, then locate the module containing your workbook creation macro.

2
Locate the Naming Code

Find the specific line of code that assigns the worksheet name. It usually looks similar to `shR.Name = shT.Cells(i, 1)`.

3
Apply the Left Function

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)`.

4
Run and Test the Macro

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.

Limit Worksheet Name Length using the VBA Left Function
Ensure Unique Names: When truncating names to 31 characters, verify that the shortened names are unique. If two different firms share the exact same first 31 characters, VBA will return a different error for duplicate worksheet names.
Free Microsoft Office alternative

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.

Free to download and use with a highly familiar user interfaceFully compatible with Microsoft Excel (.xlsx, .xlsm, .xls) files and VBA macrosEasily edit macros and handle worksheet character limits nativelyLightweight software that runs quickly, even on older devices
microsoft office alternative - wps office

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.