Excel VBA: List All Sheets in a Workbook

To list every sheet in the workbook that contains your VBA code, loop through its Sheets collection and print each name in the VBA Immediate window. The macro below includes worksheets and chart sheets; use Worksheets instead if you want worksheet tabs only.

Sub ListAllSheets()
    Dim sh As Object

    ' Print every sheet name in the workbook containing this code.
    For Each sh In ThisWorkbook.Sheets
        Debug.Print sh.Name
    Next sh
End Sub

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, show the Immediate window with Ctrl+G, then run the macro. The names appear one per line in that window. The sections below explain how to put the list on a worksheet, choose the right collection, and avoid common surprises.

What “all sheets” means in VBA

Excel uses two similar collections that are easy to confuse. The Sheets collection contains all sheet objects in a workbook: ordinary worksheets and chart sheets. A chart sheet is a full sheet tab devoted to a chart, rather than a chart embedded within a worksheet. The Worksheets collection contains worksheets only.

That distinction determines which loop to use. In the opening macro, sh is declared as an Object because a member of Sheets might be a worksheet or a chart sheet. For a worksheet-only list, declare the loop variable as a Worksheet and loop through ThisWorkbook.Worksheets:

Sub ListWorksheetsOnly()
    Dim ws As Worksheet

    ' Print worksheet names, excluding chart sheets.
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws
End Sub

If the workbook contains three worksheets named Sales, Summary, and Notes, plus a chart sheet named Trend, the first macro prints all four names. The worksheet-only macro prints the first three. An embedded chart on the Sales worksheet does not add another item to either collection.

Run the macro in Excel for Windows

Put the code in a standard VBA module in the workbook you want to inspect. A standard module is a general-purpose code container, not the code window for a particular worksheet or for ThisWorkbook. Follow these steps:

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then select Developer.
Choose Developer > Visual Basic, or press Alt+F11, to open the Visual Basic Editor.
In the editor, choose Insert > Module. Paste the macro into the new module.
Open the Immediate window with View > Immediate Window or Ctrl+G.
Click inside the macro and press F5, or choose Run > Run Sub/UserForm. Read the printed names in the Immediate window.

To run it again, leave the code in the workbook and repeat the last step. If you prefer the macro to appear in Excel’s Macro dialog, close or switch back to Excel and use Developer > Macros; select the macro and choose Run. A macro stored in a standard module is generally easier to find and run this way than code placed in a sheet event procedure.

Print sheet names into worksheet cells

The Immediate window is useful for a quick inspection, but a cell list is easier to copy, share, or use in another task. This example writes every sheet name into column A of an existing worksheet named Summary. It includes chart sheets in the list, just like the first macro.

Sub WriteSheetNamesToSummary()
    Dim sh As Object
    Dim outputWs As Worksheet
    Dim rowNumber As Long

    ' Summary must already exist in this workbook.
    Set outputWs = ThisWorkbook.Worksheets("Summary")
    rowNumber = 1

    ' Write each sheet name into the next row in column A.
    For Each sh In ThisWorkbook.Sheets
        outputWs.Cells(rowNumber, 1).Value = sh.Name
        rowNumber = rowNumber + 1
    Next sh
End Sub

Before running it, make sure cells A1 downward on Summary are available: the macro writes into them and does not clear existing contents first. If your destination worksheet has another name, replace Summary in the code with that exact tab name. If you want worksheets only, change the loop to For Each sh In ThisWorkbook.Worksheets; the object declaration can then be changed to Dim sh As Worksheet.

When you rerun the cell-writing macro after deleting sheets, any names left below the new, shorter list will remain. Clear the old output range yourself after checking that it contains no information you need, or adapt the macro to manage a dedicated output area. Avoid putting the output on a sheet whose column A contains data you want to keep.

Choose ThisWorkbook or ActiveWorkbook

The workbook reference matters as much as the collection. ThisWorkbook means the workbook that contains the running VBA code. ActiveWorkbook means the workbook currently active in Excel. If the macro is saved in the workbook being listed, ThisWorkbook is usually the clearest and safest choice.

The two references can point to different files. For example, code stored in Personal.xlsb or an Excel add-in can run while a separate customer workbook is active. In that situation, ThisWorkbook.Sheets would list sheets in Personal.xlsb or the add-in, not the customer file. If that is your intention, use ActiveWorkbook.Sheets and make sure the target workbook is active before running the macro. Do not use ActiveSheet as a substitute: it identifies one active sheet, not the workbook’s collection of sheets.

Hidden sheets, order, and names

Both Sheets and Worksheets include hidden sheets. A sheet’s visibility controls whether its tab is displayed; it does not remove that sheet from the workbook’s collection. A sheet set to xlSheetVeryHidden is also included, even though a user cannot reveal it through the ordinary Unhide dialog. The loop prints its name just as it prints a visible sheet’s name.

The collection is traversed in the workbook’s tab order, so the printed list follows the order of the tabs. To include each worksheet’s position, use its Index property; to report whether it is hidden, inspect its Visible property. Those details are not included in the simple name-listing examples because the requested output is just the names.

A sheet name can contain spaces and punctuation, so refer to it by its Name property rather than assuming it is a simple word. The property returns the displayed tab name, not the worksheet’s VBA code name. Excel limits worksheet tab names to 31 characters and disallows certain characters, but a listing macro does not need to alter or validate names: it simply reads them.

Common problems and safe handling

No output appears: The macro may have run while the Immediate window was closed. Open it with Ctrl+G in the Windows Visual Basic Editor. Also confirm that you ran the procedure containing Debug.Print, rather than another macro.
A chart sheet causes a type mismatch: This can happen if a variable declared as Worksheet is used to loop over Sheets and the workbook contains a chart sheet. Use Dim sh As Object for all sheet types, or loop through Worksheets with a Worksheet variable.
The macro lists the wrong file: Check whether the code uses ThisWorkbook or ActiveWorkbook. This is especially important when running code from Personal.xlsb or an add-in.
The cell-writing example reports a subscript error: The workbook probably has no worksheet named Summary. Change the name in the code to match an existing worksheet tab, or create the destination worksheet first.
The macro is unavailable after saving: Save the workbook in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm) or Excel Binary Workbook (.xlsb). Saving VBA code in the standard .xlsx format removes the macros.

Macros may be blocked by Excel’s security settings or by your organization’s policies. Enable a macro only when you trust the file and understand what it does; do not lower security settings globally just to run a simple listing procedure. Save a copy of the workbook before running any macro. VBA actions cannot be undone with Ctrl+Z, and the cell-writing example changes worksheet contents.

Excel for Mac and Excel for the web

The VBA collections and code shown here work in desktop Excel for Microsoft 365 on Windows and Mac. On Mac, open the Visual Basic Editor from the Developer tab’s Visual Basic command if available, or use the Visual Basic Editor command under Excel’s Tools menu. Keyboard shortcuts can vary with the Mac keyboard and function-key settings, so the ribbon or menu command is a dependable alternative to the Windows Alt+F11 shortcut. Run the procedure using the editor’s Run command or toolbar.

Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Open the file in the desktop Excel application to use this VBA approach. If you only need a one-time list and cannot run VBA, you can inspect the workbook’s visible tabs manually; web Excel does not provide a VBA-based way to enumerate them.

Frequently Asked Questions

Does ThisWorkbook.Sheets include hidden sheets?
Yes. The collection includes hidden and very hidden sheets as well as visible sheets. Visibility affects whether a tab is shown, not whether the sheet is part of the collection.

How do I list worksheet tabs but exclude chart sheets?
Loop through ThisWorkbook.Worksheets and declare the loop variable as Worksheet. The Worksheets collection excludes chart sheets.

Can VBA list sheet names in cells instead of the Immediate window?
Yes. Use a worksheet as the destination and assign each name to a cell, as in the Summary example above. Check that the destination range is available before running it, because that example writes over cells in column A.

Why does my macro show sheets from another workbook?
The code may be stored in a different workbook from the one you intend to inspect. ThisWorkbook refers to the code’s workbook; ActiveWorkbook refers to the workbook currently active in Excel.

Can I run this VBA macro in Excel for the web?
No. Excel for the web does not run VBA. Open the workbook in desktop Excel for Windows or Mac to run the macro.

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.