VBA Find Sheet Names in a Workbook: Practical Methods

To find worksheet names with VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name property. For example, the macro below prints every worksheet name in the Immediate window; change ThisWorkbook to ActiveWorkbook if you mean the workbook currently selected in Excel.

Sub ListWorksheetNames()
    Dim ws As Worksheet

    ' Print each worksheet name in this workbook.
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

Save a copy of the workbook before running a macro: VBA actions generally cannot be undone with Ctrl+Z. This example only prints names; it does not change cells or sheets.

What VBA Means by a Sheet Name

Excel exposes worksheet tabs as objects in the Worksheets collection. Each worksheet has a Name property: the text shown on its tab, such as Sales or January 2026. VBA can read that property, compare it with a name you supply, or use it to refer to a particular worksheet.

A workbook can also contain chart sheets, which hold a chart as a separate sheet rather than as a chart embedded in a worksheet. Worksheets includes only worksheets. The broader Sheets collection includes both worksheets and chart sheets. This distinction matters if “all sheet names” means every tab in the workbook, not just tabs with cells.

Use Worksheets when the macro needs to work with grid-based sheets.
Use Sheets when you need to include chart sheets as well.
Use each object’s Name property to get the tab name; CodeName is a different identifier used in VBA.

Print All Worksheet Names in the Immediate Window

The first macro is a useful way to inspect a workbook without changing it. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, paste the code into the module, and run the procedure with the cursor inside it by pressing F5. If the Immediate window is not visible, choose View > Immediate Window in the editor. The printed names appear there, one per line.

On a Mac, open the Visual Basic Editor using Tools > Macro > Visual Basic Editor, or the keyboard shortcut available for your keyboard and Excel installation. Shortcut behavior can vary with function-key settings, so the menu is a reliable route. The VBA editor’s menus may also differ slightly by release. Excel for the web cannot create, edit, or run VBA macros; open the workbook in the desktop application.

ThisWorkbook refers to the workbook containing the VBA code. That is usually the right choice when the macro is stored in the workbook whose names you want. If the macro is in Personal.xlsb or an add-in, however, ThisWorkbook refers to that macro container, not the workbook you are viewing. In that situation, use ActiveWorkbook only when the active workbook is definitely the intended target.

Find One Sheet by an Exact Name

If you need to know whether a particular worksheet exists before using it, loop through Worksheets and compare names. The following function uses a text comparison, so it treats uppercase and lowercase letters as equivalent. Put it in a standard module, then call it from another macro.

Function WorksheetExists(ByVal sheetName As String, ByVal wb As Workbook) As Boolean
    Dim ws As Worksheet

    ' Compare each worksheet name without case sensitivity.
    For Each ws In wb.Worksheets
        If StrComp(ws.Name, sheetName, vbTextCompare) = 0 Then
            WorksheetExists = True
            Exit Function
        End If
    Next ws
End Function

For example, a procedure can call WorksheetExists("Sales", ThisWorkbook). It returns True if the workbook containing the code has a worksheet named Sales, and False if it does not. Passing the workbook explicitly helps avoid ambiguity about which file is being checked. If you need case-sensitive matching, change vbTextCompare to vbBinaryCompare.

Checking first is useful before a line such as Set ws = ThisWorkbook.Worksheets("Sales"). If that exact worksheet is absent, direct reference normally raises a “Subscript out of range” error. A check lets your macro handle the missing-sheet case deliberately instead of stopping unexpectedly.

Search for Part of a Sheet Name

When the full name is unknown, use VBA’s InStr function to look for a text fragment. For example, the loop below prints the names of worksheets containing the word “budget,” regardless of capitalization.

Sub FindWorksheetsContainingText()
    Dim ws As Worksheet
    Dim searchText As String

    searchText = "budget"

    ' Search worksheet names without case sensitivity.
    For Each ws In ThisWorkbook.Worksheets
        If InStr(1, ws.Name, searchText, vbTextCompare) > 0 Then
            Debug.Print ws.Name
        End If
    Next ws
End Sub

InStr returns the position where it finds the text, or zero when there is no match. The comparison argument vbTextCompare makes the search case-insensitive. This is a substring search, not an exact-name check: a search for “budget” can match names such as “Annual Budget” and “Budget Notes.” Use StrComp instead when the entire name must match.

Include Chart Sheets and Hidden Sheets

To list every tab, including chart sheets, loop through Sheets rather than Worksheets. Because a sheet in that collection might be a worksheet or a chart sheet, declare the loop variable as Object or use another suitable general object type.

Sub ListAllSheetNames()
    Dim sh As Object

    ' Sheets includes worksheets and chart sheets.
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh
End Sub

Hidden and very hidden worksheets are still members of Worksheets, so the basic listing loop includes them. A hidden sheet is not displayed as a tab, but its Name is still readable in VBA. A very hidden sheet is generally controlled through the Visual Basic Editor rather than the ordinary Unhide dialog; that status does not remove it from the collection.

If you want to report visibility as well as names, a worksheet’s Visible property can be inspected. Its values distinguish visible, hidden, and very hidden states. Do not assume that a sheet absent from the tab strip has been deleted: it may simply be hidden. Conversely, a chart sheet can be present in Sheets but absent from a loop over Worksheets.

Choose the Correct Workbook Reference

Many errors that look like sheet-name problems are actually workbook-reference problems. Excel can have several workbooks open, and the same tab name may exist in more than one. A reference to ActiveWorkbook.Worksheets("Data") searches the workbook that currently has focus. The active workbook can change if the macro activates another file or a user switches windows.

Use ThisWorkbook for the file that stores the code. Use ActiveWorkbook when the macro is intentionally meant to work on whichever workbook is active. For a workbook opened by the macro, assign it to a Workbook variable and use that variable consistently. This makes the target explicit and reduces the chance of reading names from the wrong file.

Macro stored in the target workbook: start with ThisWorkbook.
Macro intended for the selected workbook: use ActiveWorkbook after confirming it is the target.
Macro opens or receives a specific file: store that file in a workbook variable and refer to it directly.

Common Errors and Practical Checks

“Subscript out of range” often means the requested name does not exist in the workbook object being referenced. Check spelling, spaces, punctuation, and the workbook reference. A tab that appears to read “Sales” might contain a trailing space, or the macro might be checking a different open file. Printing the names with the first loop makes these discrepancies easier to spot.

Remember that worksheet tab names have Excel’s naming restrictions, including a 31-character limit and restrictions on certain characters. VBA reads the actual name; it does not automatically shorten, correct, or interpret a misspelled name. If code builds a name from a variable, inspect the resulting string before using it as a worksheet reference.

Other useful checks include confirming that a workbook is open, that the intended sheet is not a chart sheet when your code requires a worksheet, and that a macro is not relying on the active workbook changing in the middle of a procedure. If a macro only needs a name, reading ws.Name does not require unhiding the sheet or changing its contents.

Ways to Display or Store the Names

The Immediate window is convenient for a quick inspection, but it is not a permanent report. For a reusable inventory, adapt the loop to write each name to a chosen worksheet, export it, or use it in another process. Before writing into cells, decide where the output belongs and whether existing content should be preserved; a macro that clears a column can erase useful data.

Another non-VBA option is to inspect the workbook’s visible tabs directly, but that will not reveal hidden tabs in the ordinary view. For a one-time inventory, the VBA Immediate window may be the quickest option. For repeated reporting, a macro that writes results to a dedicated index sheet is more practical, provided it handles whether that sheet already exists and does not overwrite data unexpectedly.

VBA is appropriate when the task is part of a repeatable desktop workflow, such as validating a standard set of worksheet names before processing monthly files. It is not available in Excel for the web, and it requires macros to be permitted by the organization’s security settings. Do not enable macros in an unfamiliar workbook simply to inspect its contents; use Excel’s normal interface or a trusted copy when the file’s source is uncertain.

Frequently Asked Questions

How do I list all sheet names in Excel VBA?
Loop through ThisWorkbook.Worksheets and print each ws.Name to the Immediate window. Use ThisWorkbook.Sheets instead if the list must include chart sheets too.

What is the difference between Worksheets and Sheets?
Worksheets contains grid-based worksheets. Sheets contains worksheets and chart sheets, so the latter is the broader collection when you mean every workbook tab.

Does VBA find hidden worksheet names?
Yes. Hidden and very hidden worksheets remain in the Worksheets collection, and VBA can read their Name property without making them visible.

Why does VBA say Subscript out of range for a sheet name?
The name may be misspelled, include an extra space, or belong to a different workbook than the one your code is checking. Confirm the exact tab name and whether your reference should be ThisWorkbook, ActiveWorkbook, or a particular workbook variable.

Can I run VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel application to use the Visual Basic Editor and run VBA code.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.