Fix Excel VBA Subscript Out of Range for Worksheet Names
Question details
The user needs to fix a VBA subscript-out-of-range error when a Workbook_Open procedure references a worksheet.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A Workbook_Open procedure accesses cell O2 using Worksheets("Sheet1").
- Observed behavior
- The procedure can raise error 9 when the worksheet's visible tab name differs from its VBA code name.
Open the workbook in Excel and check the exact name on the worksheet tab. If the Developer tab is not visible, enable it in Excel's ribbon settings so you can open the Visual Basic Editor.
Reference the exact worksheet tab name
Use this approach when the code calls Worksheets("Sheet1") but the visible sheet tab has a different name.
In Excel, find the worksheet tab and note its exact visible name, including spaces and punctuation.
Open Developer > Visual Basic and locate the Workbook_Open procedure under ThisWorkbook. Replace the sheet reference with the actual tab name, for example: ThisWorkbook.Worksheets("June 19").Range("O2"). Use the expression in the existing statement that reads or writes the cell.
In the Visual Basic Editor, choose Insert > Module and add a test procedure: Sub TestSheet(): Debug.Print ThisWorkbook.Worksheets("June 19").Range("O2").Value: End Sub. After the workbook has opened, run TestSheet and check View > Immediate Window for the value.

Reference the worksheet by its VBA code name
Use the code name when you want the reference to remain valid if the worksheet tab is renamed.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and compatibility with common Microsoft Office formats, making it easy to move everyday files. Use Microsoft Excel to debug Excel-specific VBA errors; macro support and behavior may differ in WPS.

Frequently Asked Questions
Why does Worksheets("Sheet1") fail when I can see Sheet1 in the VBA editor?
Sheet1 may be the worksheet's VBA code name rather than its visible tab name. Check the tab in Excel or select the sheet in the Visual Basic Editor and compare its (Name) and Name properties.
What is the difference between a worksheet name and a code name?
The worksheet name appears on the Excel tab and is used in Worksheets("name"). The code name appears as the (Name) property in the Visual Basic Editor and can be used directly, for example Sheet1.Range("O2").
How do I make sure VBA looks in the right workbook?
Qualify the reference with the workbook that contains the sheet. Use ThisWorkbook.Worksheets("June 19") when the sheet is in the workbook containing the code, or specify the other open workbook if it is elsewhere.
What if the reference works after the workbook opens but fails in Workbook_Open?
Debug Workbook_Open to find the failing line and check whether the required workbook and sheet are available at that point. If the failure depends on timing, a standard-module procedure can be scheduled with Application.OnTime to run shortly after opening.




