VBA & Macro Problems

Fix Excel VBA Subscript Out of Range for Worksheet Names

Amos GikundaAmos Gikunda Nov 14, 2026 870 views

Question details

The user needs to fix a VBA subscript-out-of-range error when a Workbook_Open procedure references a worksheet.

Fix Excel VBA Subscript Out of Range for Worksheet Names
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.
Before you start

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.

Solution 1Recommended

Reference the exact worksheet tab name

Use this approach when the code calls Worksheets("Sheet1") but the visible sheet tab has a different name.

1
Check the sheet tab

In Excel, find the worksheet tab and note its exact visible name, including spaces and punctuation.

2
Update the VBA reference

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.

3
Test after opening

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 exact worksheet tab name
Check the workbook too: ThisWorkbook refers to the workbook containing the VBA code. If the worksheet is in another open workbook, qualify the reference with that workbook instead. If the test succeeds but Workbook_Open still fails, debug the procedure to identify the exact line causing the error.
Free Microsoft Office alternative

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.

Open and edit spreadsheets in a familiar interfaceCompatible with common Microsoft Office formats, including XLSX, DOCX, and PPTXReview worksheet tabs and cell data for everyday spreadsheet workFree and lightweight for working across office document types
microsoft office alternative - wps office

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.