Runtime Error 9: Subscript out of range (Calling a Worksheet that doesn’t exist).

Excel VBA throws Runtime Error 9: Subscript out of range when your code attempts to access an element of a collection or an array index that does not exist. When calling a worksheet, this error almost always means VBA cannot find a sheet tab matching the exact name provided in your code, or your code is querying the wrong workbook collection.

What Causes Runtime Error 9 When Calling a Worksheet in VBA?

Runtime Error 9 occurs when VBA requests a worksheet name or index number that is absent from the target workbook. Common triggers include typographical errors in the sheet name, hidden leading or trailing spaces on the tab, calling an unreferenced workbook, or referencing an index number that exceeds the total count of sheets.

The Quick Fix: Verify Name and Workbook Context

Check the line highlighted in yellow when the error breaks execution:

' BROKEN: Fails if the tab has a trailing space or is misspelled
Set ws = Worksheets("Data")

' BROKEN: Fails if the active workbook does not contain "Data"
Set ws = Worksheets("Data")

' FIXED: Explicitly bind the parent workbook and verify exact spelling
Set ws = ThisWorkbook.Worksheets("Data")

If the name looks correct on your screen, check the actual tab in Excel. A tab named "Data " (with a trailing space) will fail when called as Worksheets("Data").

---

Primary Triggers and How to Fix Them

1. Typographical Errors and Ghost Spaces in Tab Names

VBA string matching for worksheet names is case-insensitive, but it requires an exact character match. Extra spaces are the most common source of hidden failures:

' User sees "Reports" on the tab, but someone typed "Reports " or " Reports"
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Reports") ' Throws Runtime Error 9

To diagnose this quickly:

1. Double-click the worksheet tab in Excel.
2. Press the right arrow key to see if the cursor travels past the visible letters.
3. Rename the tab cleanly, or use the tab’s CodeName instead of its display name.

2. Implicit ActiveWorkbook Referencing

Writing Worksheets("Sheet1") without qualifying the parent workbook tells VBA to search ActiveWorkbook.Worksheets. If a user clicks another workbook window, or if your code opens a CSV or secondary file, the active workbook shifts:

' DANGEROUS: Searches whichever file is currently active on screen
Dim ws As Worksheet
Set ws = Worksheets("MasterData")

' SAFE: Explicitly targets the workbook containing the code
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("MasterData")

' SAFE: Explicitly targets an external opened workbook
Dim wb As Workbook
Set wb = Workbooks.Open("C:\Reports\Monthly.xlsx")
Set ws = wb.Worksheets("Summary")

If you omit the workbook object and attempt to access ranges on the wrong sheet, you can trigger downstream errors like Runtime Error 1004: Method ‘Range’ of object ‘_Worksheet’ failed..

3. Using VBA Sheet CodeNames vs. Tab Names

Excel gives every worksheet two names:

• Tab Name (Name): What users see on the tab (can be edited by users).
• CodeName ((Name) in the Properties window): Hardcoded in the VBA Project tree (cannot be changed from the Excel UI).

If your users frequently rename tabs, avoid Worksheets("TabName") entirely and call the CodeName directly:

' If the sheet's CodeName is Sheet1, call it directly without Quotes or Worksheets()
Sheet1.Range("A1").Value = "Updated"

This bypasses collection indexing completely, making Runtime Error 9 impossible on that call.

4. Index Out of Range (Worksheets(i))

When referencing sheets by position, using an index higher than the sheet count triggers Error 9:

Dim i As Long
For i = 1 To 10
    ' If the workbook only has 3 sheets, this crashes on i = 4
    Worksheets(i).Cells(1, 1).Value = "Processed"
Next i

' SAFE: Bound the loop to the actual collection count
For i = 1 To ThisWorkbook.Worksheets.Count
    ThisWorkbook.Worksheets(i).Cells(1, 1).Value = "Processed"
Next i

Note that Sheets.Count includes Chart sheets, while Worksheets.Count includes only standard grid sheets. Calling Worksheets(i) on an index that points to a Chart sheet will fail.

---

Defensive Coding: How to Check If a Worksheet Exists

To prevent Runtime Error 9 from halting automation when handling dynamic or user-generated sheet names, test whether the sheet exists before attempting to assign it:

Public Function WorksheetExists(sheetName As String, Optional wb As Workbook) As Boolean
    Dim ws As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook

    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0

    WorksheetExists = Not ws Is Nothing
End Function

Use this function inside your procedures:

Sub ProcessMonthlyData()
    Dim targetSheet As String
    targetSheet = "October_2026"

    If WorksheetExists(targetSheet) Then
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets(targetSheet)
        ws.Range("A1").Value = "Audit Complete"
    Else
        MsgBox "The required worksheet '" & targetSheet & "' was not found.", vbCritical
        Exit Sub
    End If
End Sub

If your assignment still fails despite a valid sheet name, ensure your target variable was assigned using the Set keyword. For more details on uninstantiated variables, see Runtime Error 91: Object variable or With block variable not set (The Set keyword trap)..

---

Next Steps and Diagnostic Escalation

When debugging an unexpected Error 9:

1. Inspect variable values: Check string variables passed to Worksheets() using the Immediate Window. See How to use the Immediate Window to debug variable values in real-time..
2. Prevent undeclared typos: Enforce explicit variable definitions at compile time. Review “Variable not defined”: Why you must use Option Explicit..
3. Structured Recovery: Implement a formal error trap rather than leaving macros unhandled. See Building a Global Error Handler: The Err.Number and Err.Description guide..
4. General Variable Issues: If you suspect a broader variable mismatch across procedures, review the complete diagnostic map in VBA Logic & Variable Errors: Troubleshooting Runtime Codes and Syntax Breaks.

---