Excel VBA List Worksheet Names: Methods and Examples

To list worksheet names with Excel VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name property. You can print the names in the VBA editor’s Immediate window or write them into cells; the examples below show both approaches and explain how to handle hidden sheets and avoid overwriting data.

How the Worksheets collection works

In VBA, a workbook’s Worksheets collection contains its worksheet tabs: the sheets made up of rows and columns. A For Each loop visits those worksheets one at a time, and ws.Name returns the displayed tab name as text. For example, if a workbook has tabs named Summary, January, and Archive, looping through its worksheets returns those names in their tab order.

The collection includes worksheets that are hidden as well as visible ones. A sheet’s visibility setting affects whether a user can see its tab, not whether VBA can find it in Worksheets. This makes a loop useful for inventorying a workbook, including sheets hidden for presentation or administrative reasons.

One important distinction: Worksheets does not include chart sheets, which contain a chart as a separate sheet rather than cells. If you mean every kind of sheet tab, including chart sheets, use the broader Sheets collection instead. For a request specifically about worksheet names, Worksheets is the precise choice.

Write worksheet names into an Index sheet

To create a visible list in the workbook, first add a worksheet named Index. The macro below clears the contents of column A on that sheet, then writes the other worksheet names from cell A1 downward. It deliberately leaves the Index sheet out of its own list.

Sub ListWorksheetNames()
    Dim ws As Worksheet
    Dim outputSheet As Worksheet
    Dim rowNumber As Long

    ' Set up a worksheet named Index before running this macro.
    Set outputSheet = ThisWorkbook.Worksheets("Index")
    outputSheet.Columns("A").ClearContents
    rowNumber = 1

    ' Write each worksheet name, except the output sheet.
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> outputSheet.Name Then
            outputSheet.Cells(rowNumber, 1).Value = ws.Name
            rowNumber = rowNumber + 1
        End If
    Next ws
End Sub

The first result goes in A1, the next in A2, and so on. The macro does not sort names alphabetically; it follows the workbook’s tab order. To include the Index worksheet too, remove the If condition and its matching End If, leaving the assignment and row counter inside the loop.

Before running it, save a copy of the workbook. Macros cannot be undone with Ctrl+Z, and this macro clears existing contents throughout column A on Index. Keep that column reserved for the generated list, or change the output column and clearing line to suit your layout. ClearContents removes values and formulas but keeps cell formatting.

Where to put and run the macro

In Excel for Microsoft 365 on Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, paste the code into the new module, and return to Excel. You can run the macro from the editor by placing the cursor inside the procedure and pressing F5, or run it from Excel using Developer > Macros, selecting ListWorksheetNames, and choosing Run.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer in the main tabs list. On Excel for Mac, VBA is available in the desktop application; the editor can be opened from Tools > Macro > Visual Basic Editor. Option+F11 is a common Mac shortcut, though some keyboards also require the Fn key. Excel for the web can open a macro-enabled workbook but does not run or edit VBA macros; use desktop Excel for these steps.

To keep VBA in the workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). If you save as a standard .xlsx file, VBA code is not retained. Excel may also display a security notice when opening a macro-enabled file. Only enable macros in files you trust; macro security is a protection against code you did not intend to run.

Use the Immediate window for a quick list

If you only need to inspect names rather than keep a list on a worksheet, print them to the Immediate window. This version does not change any cells:

Sub PrintWorksheetNames()
    Dim ws As Worksheet

    ' Print worksheet names in workbook tab order.
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

In the Visual Basic Editor, show the Immediate window with View > Immediate Window, then run the procedure. Each name appears on its own line. This is handy for a quick check or debugging, but the output is temporary: it is not saved as a worksheet list. Use the Index-sheet version when you need a list that other users can see or that another process can reference.

Choose the correct workbook and output behavior

The examples use ThisWorkbook, which means the workbook containing the VBA code. That is often the safest choice when the macro is stored in the same file as the worksheets being listed. By contrast, ActiveWorkbook refers to the workbook currently active in Excel. If code is stored in a Personal Macro Workbook or an add-in, ThisWorkbook may point to that container rather than the file you want to inspect. In that situation, deliberately choose the intended workbook, for example by assigning ActiveWorkbook to a workbook variable after confirming the correct file is active.

Think about where results should go before running a listing macro. Writing to an existing sheet is predictable, but the destination range must be safe to clear or overwrite. Writing to the active sheet can be convenient for a one-off task, yet it can overwrite data and may list the destination sheet among the results. A dedicated Index sheet makes the destination explicit and keeps the list separate from source data.

For a permanent, readable index: use a dedicated output worksheet and reserve its chosen column for the list.
For a quick diagnostic: use Debug.Print and the Immediate window.
For a complete tab inventory: decide whether chart sheets should count; use Worksheets for worksheets only and Sheets for all sheet types.

Common problems and practical fixes

Run-time error 9, Subscript out of range: the workbook does not contain a worksheet whose name exactly matches Index, or the code is looking in the wrong workbook. Check the tab spelling, including spaces, and confirm that the code’s ThisWorkbook reference points to the intended file. Create the Index sheet before running the sample macro.

The output is missing a sheet name: the sample intentionally excludes Index. Remove the condition if you want that worksheet included. Also remember that a hidden worksheet is still in the collection, so a name may appear even when its tab is not visible.

The macro seems to do nothing: check that you ran the correct procedure and that macros are enabled for this trusted workbook. With the Immediate-window version, make sure the Immediate window is open; otherwise, the names may have printed without being visible to you. In the Index version, inspect column A on the Index sheet.

Existing information disappeared: the sample clears all contents in column A on Index before writing the list. This is why the destination column should be reserved and the workbook copied first. If you want to preserve existing content, change the output location or adapt the macro so it clears only a specifically reserved range.

Names appear in an unexpected order: VBA follows the current left-to-right tab order, not alphabetical order. Reorder the worksheet tabs if tab order is what you want, or add separate sorting logic if the list must be alphabetical. Listing names and sorting those results are different tasks.

Alternatives to VBA

For a small workbook, reading the tab names manually may be quicker than setting up a macro. For a repeatable inventory, VBA is useful because the same loop can be run again after sheets are added, renamed, hidden, or deleted. The Immediate-window method is best for inspection; writing names to a sheet is better when the list needs to remain in the file.

There is no ordinary worksheet formula that directly returns every worksheet tab name as a dynamic list in all Excel versions. Formula-based name extraction methods generally depend on indirect references or workbook metadata and can be more fragile than a short macro. If VBA is unavailable, a manual list or a suitable Office Script in an environment that supports Office Scripts may be an option, but Office Scripts use TypeScript rather than VBA and are a separate feature with different availability.

Frequently Asked Questions

Does the VBA loop include hidden worksheets?
Yes. The Worksheets collection includes visible, hidden, and very hidden worksheets. The code lists their names regardless of visibility.

How can I list chart sheet names too?
Loop through the workbook’s Sheets collection rather than Worksheets. A workbook can contain worksheet tabs and chart sheets, so the broader collection includes both types.

Can I make the list alphabetical?
The examples return names in tab order. To produce alphabetical output, collect the names and sort them before writing them, or sort the completed list on the Index sheet. The basic loop does not sort.

Will the macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the macro-enabled workbook in desktop Excel for Windows or Mac to run the code.

Can the macro list worksheet names without changing the workbook?
Yes. Run the PrintWorksheetNames procedure and view the results in the VBA editor’s Immediate window. Unlike the Index-sheet macro, it does not write to or clear worksheet cells.

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.