🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Worksheets in Excel — Reference a Sheet So Renaming a Tab Cannot Break It

|

VBA Worksheets in Excel — Reference a Sheet So Renaming a Tab Cannot Break It

TL;DR — Worksheets is a collection, and you address a sheet inside it three ways: by name (Worksheets("Jan")), by index (Worksheets(1)), or by CodeName (Sheet1). The name breaks the instant a user renames the tab; the index breaks the instant someone drags the tabs into a new order; the CodeName breaks on neither, because only you can change it. Qualify the collection with a workbook, Set the sheet into a variable once, and prefer the CodeName for tabs you control.

Sub ThreeWaysToNameASheet()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Jan")   ' by name  - breaks if the tab is renamed
    Set ws = ThisWorkbook.Worksheets(1)        ' by index - breaks if tabs are reordered
    Set ws = Sheet1                            ' by CodeName - survives both

    ws.Range("A1").Value = "Report"
End Sub

What you'll learn

  • The mental model — Worksheets is a collection you can address three different ways
  • The three reference tools, and where the Worksheets collection sits among them
  • Worksheets versus Sheets, and the chart-sheet difference that actually matters
  • The three ways to name a sheet, and exactly what breaks each one
  • The silent trap — why a working macro dies the day a user renames a tab
  • The opinion — Set once, qualified, and prefer the CodeName

The mental model: a collection, addressed three ways

Worksheets is not a single object — it is the collection of all the worksheets in a workbook. To do anything to one sheet, you pull it out of the collection by an identifier. VBA gives you three identifiers, and they are not equally sturdy:

  1. By name — Worksheets("Jan") — the text on the tab. Readable, and the most common. Also the most fragile, because the tab name is the one thing your users change freely.
  2. By index — Worksheets(1) — the position, left to right. Handy for "the first sheet," useless once anyone drags the tabs around.
  3. By CodeName — Sheet1 — an internal name only a developer can change, in the Properties window. Invisible to users, so it survives anything they do to the tabs.

Choosing which identifier to use is choosing how fragile your macro will be. That is the whole lesson.

The Worksheets collection among the three reference tools

Naming the sheet is only half a reference — you also have to say which workbook it lives in. That is where this cluster's other two tools come in.

Tool What it points at Pair it with
ThisWorkbook The workbook that holds this code — fixed .Worksheets("Jan") to reach a specific tab in your own file
ActiveWorkbook / ActiveSheet Whatever is on top right now — fickle Rarely — only for generic tools
Worksheets(...) A named sheet inside a workbook Always a workbook in front of it — ThisWorkbook.Worksheets(...)

The rule that ties the cluster together applies here too: an unqualified Worksheets("Jan") reaches into the active workbook. Put a workbook in front of it — ThisWorkbook.Worksheets("Jan") — so the sheet is looked up in the file you mean, not whatever the user last clicked.

Worksheets vs Sheets: the difference that bites

Worksheets and Sheets look interchangeable and usually behave that way, but they are not the same collection. Sheets contains every tab — worksheets and chart sheets. Worksheets contains only the grid sheets you type into.

Debug.Print ThisWorkbook.Worksheets.Count   ' grid sheets only
Debug.Print ThisWorkbook.Sheets.Count        ' grid sheets + chart sheets

Most workbooks have no chart sheets, so the two counts match and nobody notices. Add one chart sheet and the numbers diverge — and a loop over Sheets that assumes every item is a worksheet will hit the chart sheet and raise an error on a worksheet-only property. Use Worksheets when you mean grid sheets, which is almost always. Reach for Sheets only when you deliberately want chart sheets in the loop too.

The three ways to name a sheet, and what breaks each

Line them up against the two things users and developers actually do — rename tabs, and reorder them:

' By name: readable, but the tab name is user-editable
Set ws = ThisWorkbook.Worksheets("Jan")
' user renames the tab to "January"  ->  Subscript out of range

' By index: position-based, fine until the tabs move
Set ws = ThisWorkbook.Worksheets(1)
' user drags "Summary" to the front  ->  now points at the wrong sheet

' By CodeName: developer-only internal name, invisible to users
Set ws = Sheet1
' rename the tab, reorder the tabs  ->  still the same sheet

The name is the friendliest to read and the easiest for a user to break. The index survives renames but not reordering. The CodeName — set once in the VBA editor's Properties window under (Name) — is the only one that survives both, because there is no button in the Excel UI that changes it. For any sheet that ships as part of your tool, the CodeName is the sturdy reference.

The silent trap: a macro that dies on a rename

This is the failure that generates the support ticket. Your macro reads Worksheets("Data") and works for months. A user tidies up and renames the tab from Data to 2026 Data. Nothing in your code changed, yet every run now stops with Subscript out of range on the line that names the sheet — and the message points at the lookup, not at the rename that actually caused it.

' FRAGILE - one rename away from breaking
ThisWorkbook.Worksheets("Data").Range("A1").Value = total

' STURDY - CodeName cannot be changed from the Excel UI
Sheet1.Range("A1").Value = total

If you must key off the tab name (for sheets the user creates), guard it: check the sheet exists before you touch it, or loop the collection and match. But for the sheets you own, skip the fragility entirely and use the CodeName. It is the difference between a macro that survives a tidy-up and one that does not.

The opinion: Set once, qualified, prefer the CodeName

Address a sheet once, at the top of the macro, into a Worksheet variable — qualified by a workbook — and work through that variable everywhere after. It reads better, it is faster than re-looking-up the sheet on every line, and it puts the one fragile reference in exactly one place instead of scattering it through the code.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Jan")   ' one lookup, qualified
ws.Range("A1").Value = 100
ws.Range("A2").Value = 200                 ' no re-lookup, no ambiguity

For sheets you control, make that reference the CodeName (Set ws = Sheet1) so a user renaming the tab never reaches your code. Save the name string for sheets the user owns, and guard those. The goal is the same one that runs through this whole cluster: say exactly which object you mean, once, and never leave the macro guessing.

When you would rather describe the sheet than chase it

Half the battle with sheets is bookkeeping — which tab, which name, does it still exist, which workbook is it in. ExcelMaster lets you skip the bookkeeping: describe the outcome — "for every monthly tab in this file, total column D onto the summary sheet" — and it writes and runs code that qualifies each reference and handles missing or renamed sheets, backing up your workbook first. You name the result you want; it resolves the sheets safely so a rename or a reorder does not take the whole macro down.

Frequently asked questions

What is the difference between Worksheets and Sheets in VBA?

Worksheets is the collection of grid worksheets only. Sheets is the collection of all tabs, including chart sheets. They return the same thing when a workbook has no chart sheets, but a loop over Sheets that assumes every item is a worksheet will error on a chart sheet. Use Worksheets unless you specifically want chart sheets included.

How do I reference a worksheet by name in VBA?

Use ThisWorkbook.Worksheets("SheetName"), with the workbook in front so the lookup happens in the file you mean. The name must match the tab text exactly, and if the sheet does not exist you get Subscript out of range. For sheets you control, the CodeName is a sturdier reference because users cannot change it.

What is a worksheet CodeName in VBA?

The CodeName is a sheet's internal name — like Sheet1 — that you set in the VBA editor's Properties window under (Name). Unlike the tab name, there is no way for a user to change it from the Excel interface, so a reference like Sheet1.Range("A1") keeps working even after the tab is renamed or moved.

How do I set a worksheet to a variable in VBA?

Declare it with Dim ws As Worksheet, then Set ws = ThisWorkbook.Worksheets("Jan") (or Set ws = Sheet1 for the CodeName). Setting it once, qualified by a workbook, means the rest of your macro works through ws without re-looking-up the sheet or depending on what is active.

Why do I get Subscript out of range on Worksheets?

Because the name or index you asked for is not in the collection — usually a tab that was renamed or deleted, or a name typed with the wrong spelling or extra spaces. Check the sheet exists before you touch it, or reference it by CodeName so a rename cannot break the lookup.

Tested in

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

Related guides: VBA ThisWorkbook · VBA ActiveWorkbook · VBA Worksheet · VBA Range · VBA Cells