VBA Worksheet in Excel — Reference a Sheet by Name, CodeName or Index (and Which to Trust)

TL;DR — A worksheet has three handles and only one is safe. Sheets("Jan") uses the tab name — the user can rename it and your macro dies with "subscript out of range." Sheets(1) uses the position — drag a tab and your macro silently writes to the wrong sheet. Sheet1 (the CodeName, set in the VBE) is the one the user can't touch from Excel, so it never breaks. Grab a reference once — Set ws = ThisWorkbook.Worksheets("Data") — and stop using .Select/.Activate.

A worksheet sits one level down from the workbook: the workbook holds sheets, a sheet holds ranges. The recurring pain isn't using a sheet — it's naming it. Get the reference wrong and your macro either crashes loudly or, worse, writes to the wrong tab without a single error.

What you'll learn

• The three ways to reference a sheet and which two betray you
• Why CodeName is the bulletproof handle (and where to set it)
• The difference between Sheets and Worksheets (they are not synonyms)
• Why .Select and .Activate are macro-recorder habits, not real code

The mental model: three handles, two of them move

A sheet is one object you can grab three different ways. Two of those handles are attached to things the user controls — the tab name and the tab order — so they move out from under you. The third, the CodeName, is attached to something only you control from the VBE.

Sub ThreeWaysToGrabASheet()
    ' 1) By TAB NAME — user can rename it -> breaks
    Debug.Print Worksheets("Jan").Name

    ' 2) By INDEX (position) — user can drag-reorder -> silently wrong sheet
    Debug.Print Worksheets(1).Name

    ' 3) By CODENAME — set in the VBE, invisible to the user -> bulletproof
    Debug.Print Sheet1.Name     ' "Sheet1" is the CodeName, not the tab text
End Sub

Why this matters: handles #1 and #2 depend on user behaviour. Someone renames "Jan" to "January", or drags it after "Feb", and your macro either throws run-time error 9 ("subscript out of range") or — far more dangerous — keeps running and edits the wrong tab. Handle #3 is immune because the CodeName lives in code, not on the tab.

The rule: set a CodeName, or catch the reference once

The failure mode is using a moving handle deep inside your macro. The fix is to pin the sheet down once, at the top, and then only ever talk to that variable.

Sub SafeSheetReference()
    ' Catch the reference once — fully qualified to the workbook
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")

    ' From here on, everything goes through ws. No "active", no re-lookup.
    ws.Range("A1").Value = "Report"
    ws.Range("A2:A100").ClearContents
    Debug.Print ws.UsedRange.Rows.Count
End Sub

To make the name itself unbreakable, give the sheet a CodeName: in the VBE, select the sheet under Microsoft Excel Objects, open the Properties window (F4), and set (Name). Now you can write Set ws = SalesData and renaming the tab in Excel changes nothing. The judgment call: if a macro must survive other people using the file, reference sheets by CodeName — tab names are user content, not code.

Sheets vs Worksheets — not the same collection

This trips up almost everyone. Worksheets is only the grid sheets. Sheets is everything with a tab — including chart sheets. Loop over the wrong one and you'll hit a chart sheet that has no .Range, and crash.

Sub SheetsVsWorksheets()
    ' Worksheets: grid sheets ONLY — safe to touch .Range / .Cells
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("A1").Value = "Audited"
    Next ws

    ' Sheets: grid sheets PLUS chart sheets — .Range on a chart sheet = crash
    Debug.Print ThisWorkbook.Worksheets.Count   ' grids only
    Debug.Print ThisWorkbook.Sheets.Count        ' grids + charts
End Sub

The rule of thumb: if your loop touches cells, iterate Worksheets, never Sheets. Reach for Sheets only when you genuinely need chart sheets in the mix.

Stop using .Select and .Activate

The macro recorder writes Sheets("Data").Select then Range("A1").Select because it can only record what you clicked. Real code skips the clicking entirely — it talks to the sheet object directly. Selecting is slow, flickers the screen, and (because it changes the ActiveSheet) sets you up for exactly the wrong-sheet bugs above.

' Recorder style — selects, then acts on the selection (fragile)
Sheets("Data").Select
Range("A1").Select
Selection.Value = 100

' Real code — one line, no selection, no ActiveSheet dependency
ThisWorkbook.Worksheets("Data").Range("A1").Value = 100

You almost never need to select a sheet to read or write it. See VBA ActiveSheet for why "active" is a trap in the first place.

How ExcelMaster helps

Most multi-sheet VBA exists to shuffle data between tabs on a schedule. ExcelMaster does that from a plain-English description — "copy the cleaned rows from Data into the Summary tab and refresh the totals" — with no sheet references to misname. You skip the whole "subscript out of range" class of bug.

VBA still earns its place for always-on, in-workbook automation. But for the everyday "move this from that tab to this tab," describing it is faster and doesn't break when someone renames a sheet.

Frequently asked questions

Why do I get "subscript out of range" when referencing a worksheet?

The tab name you passed doesn't exist — usually because someone renamed the sheet, or there's a trailing space or typo. Reference by CodeName instead (set in the VBE), which the user can't change, or wrap the lookup and verify the sheet exists first.

What is a worksheet CodeName and where do I set it?

The CodeName is the internal name VBA uses (Sheet1, Sheet2, …), shown in the VBE Project Explorer. Set it via the Properties window (F4) → (Name). Unlike the tab name, the user can't change it from Excel, so it's the most reliable handle.

What's the difference between Sheets and Worksheets in VBA?

Worksheets contains only standard grid sheets. Sheets contains all sheet types, including chart sheets. If your code uses .Range or .Cells, loop over Worksheets — a chart sheet has no cells and will crash.

Do I need to Select or Activate a sheet to edit it?

No. ThisWorkbook.Worksheets("Data").Range("A1").Value = 1 works without selecting anything. .Select/.Activate are macro-recorder artifacts that slow the macro down and create wrong-sheet bugs.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-06-13.

Related guides: VBA Workbook · VBA ActiveSheet · VBA Range · VBA For Loop · VBA Dictionary