The Worksheet Object

A sheet is a thing, not a word

Working by hand you click a tab and start typing. VBA has no hands, so it needs a way to say which sheet it means, and that way is the Worksheet object. Every workbook owns a collection called Worksheets; you pick one out of it by tab name, Worksheets("Data"), or by position, Worksheets(1). Back comes an object with a name, an index, a used range and a few hundred other properties.

Worksheets or Sheets?

• Worksheets — normal grid sheets only. What you want almost every time.
• Sheets — grid sheets and chart sheets mixed, so Sheets(2) may not be a grid at all and .Range on it fails.
• By name — Worksheets("Data") reads well, and breaks when somebody renames the tab.
• By index — Worksheets(1) is simply the leftmost tab, so it breaks the moment somebody drags one.

A macro that shows a sheet describing itself

Paste this into a module (Alt+F11 → Insert → Module) and press F5. It builds a data sheet, then a second sheet listing every worksheet with its tab name, code name, position, visibility and last used row — and finally renames the data tab to prove a point.

Option Explicit

Sub WorksheetObjectTour()
    Dim wsData As Worksheet
    Dim wsReport As Worksheet
    Dim ws As Worksheet
    Dim stamp As String
    Dim i As Long
    Dim r As Long

    stamp = Format(Now, "hh-mm-ss")

    ' --- a data sheet, built by the macro itself ---
    Set wsData = ThisWorkbook.Worksheets.Add
    wsData.Name = "Data " & stamp
    wsData.Range("A1:B1").Value = Array("Region", "Revenue")

    For i = 1 To 8
        wsData.Cells(i + 1, 1).Value = "Region " & i
        wsData.Cells(i + 1, 2).Value = 1200 * i
    Next i

    wsData.Range("A1:B1").Font.Bold = True

    ' --- a report sheet, listing every worksheet in this workbook ---
    Set wsReport = ThisWorkbook.Worksheets.Add(After:=wsData)
    wsReport.Name = "Sheets " & stamp
    wsReport.Range("A1:E1").Value = _
        Array("Tab name", "Code name", "Index", "Visible", "Last used row")

    r = 1
    For Each ws In ThisWorkbook.Worksheets
        r = r + 1
        wsReport.Cells(r, 1).Value = ws.Name
        wsReport.Cells(r, 2).Value = ws.CodeName
        wsReport.Cells(r, 3).Value = ws.Index
        wsReport.Cells(r, 4).Value = (ws.Visible = xlSheetVisible)
        wsReport.Cells(r, 5).Value = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    Next ws

    wsReport.Range("A1:E1").Font.Bold = True
    wsReport.Columns("A:E").AutoFit

    ' --- rename the data tab, then keep writing to it ---
    wsData.Name = "Renamed " & stamp
    wsData.Range("D1").Value = "Written after the rename"
    wsData.Columns("D").AutoFit

    MsgBox "Listed " & (r - 1) & " worksheets. The data tab is now called " & _
        wsData.Name & ", and the variable still points at it.", _
        vbInformation, "Worksheet tour"
End Sub

Line by line

• Set wsData = ThisWorkbook.Worksheets.Add — Add hands back the new sheet, so you capture it in the same statement. Objects need Set.
• Worksheets.Add(After:=wsData) — a named argument decides where the tab lands. Without it, new sheets appear in front of the active one.
• ws.Name is the tab; ws.CodeName the internal name; ws.Index the position from the left.
• ws.Cells(ws.Rows.Count, 1).End(xlUp).Row — from the bottom of column A, jump up to the last filled cell. ws.Rows.Count is qualified too: the sheet is asked how many rows it has.
• ws.Visible = xlSheetVisible is a comparison here, not an assignment; it yields the True or False that lands in the cell.
• wsData.Name = "Renamed " & stamp — and the lines after it still write to the right sheet. The variable holds the sheet itself, not its name, so a rename cannot break it.

The code name never changes underneath you

In the Project Explorer you see entries like Sheet1 (Data). The bracketed half is the tab; the half in front is the code name, which you can type straight into your code as if it were a variable: Sheet1.Range("A1").Value = 10. Renaming the tab leaves it untouched, and F4 lets you set it to something meaningful such as shData. One limitation worth knowing: code names work only for sheets in the workbook holding the code.

The trap: error 9, subscript out of range

The most common run-time error in Excel VBA, and its message tells you nothing useful. It means the name you asked for is not in the collection — usually an invisible trailing space in the tab name, or a colleague who renamed Data to Daten:

' Two of these three die with run-time error 9, subscript out of range:
Worksheets("Data ").Range("A1").Value = 1     ' trailing space in the tab name
Worksheets("data").Range("A1").Value = 1      ' this one is fine - names ignore case
Worksheets("Daten").Range("A1").Value = 1     ' someone renamed the tab last week

' Does the sheet exist? Ask before you use it:
Dim ws As Worksheet
On Error Resume Next
Set ws = ThisWorkbook.Worksheets("Data")
On Error GoTo 0

If ws Is Nothing Then
    MsgBox "There is no sheet called Data in this workbook.", vbExclamation
    Exit Sub
End If

Why that costs an hour

The macro dies on a line that looks perfect, and the tab in front of you seems to be called exactly what you typed. Two habits kill it for good: refer to important sheets by their code name, and where you must use a tab name, ask whether the sheet exists instead of hoping. The On Error Resume Next pattern above is the standard way to ask — and the On Error GoTo 0 after it is not optional.

How it goes on

The next lesson turns those habits into rules: why a bare Range is a guess, and the existence check written out properly. After that come adding, deleting and copying sheets without the confirmation pop-up, ThisWorkbook versus ActiveWorkbook, opening and closing other files safely, looping over sheets, and protection that still lets macros work.