Excel like a finance pro.
VBA · Objects
Worksheets collection
Refers to sheets by name or index; the code name (in the editor) is stable even if the tab is renamed.
When to use it
Worksheets("Name") refers to a sheet by tab name, Worksheets(1) by position, and the code name (Sheet1, shown in the editor) refers to it even after the tab is renamed.
The code
- Code
Worksheets("Data").Range("A1") Sheet1.Range("A1") ' code name
Worked examples
By name
Worksheets("Data").Range("A1") → By tab name
Breaks if the user renames the tab.
By code name
Sheet1.Range("A1") ' code name → By the stable code name
Rename the code name in the Properties window to something meaningful.
All sheets
For Each ws In ThisWorkbook.Worksheets Debug.Print ws.Name Next → Lists every sheet
The collection.
Worth knowing
- Sheets includes chart sheets; Worksheets does not.
- Test existence with a Function that loops the collection and catches the error.
- Worksheets.Add(After:=Worksheets(Worksheets.Count)) appends a sheet.
Where it goes wrong
- A renamed tab breaks Worksheets("Name") with subscript out of range (error 9).
- Code names cannot be used for sheets in other workbooks.
Related
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.