Excel VBA: Get the Number of Sheets in a Workbook Easily

To get the number of sheets in a workbook with VBA, use the workbook’s Sheets.Count property—for example, ThisWorkbook.Sheets.Count. Use Worksheets.Count instead if you want to count worksheet tabs only and exclude chart sheets.

Basic VBA code to count sheets

In Microsoft Excel for Microsoft 365 on Windows, a short macro can read the count and display it in a message box. The example below counts every sheet in the workbook that contains the VBA code:

Sub ShowSheetCount()
    Dim wb As Workbook
    Dim sheetCount As Long

    ' ThisWorkbook means the workbook containing this macro.
    Set wb = ThisWorkbook
    sheetCount = wb.Sheets.Count

    MsgBox "This workbook contains " & sheetCount & " sheets.", vbInformation
End Sub

The Count property returns a number; it does not change the workbook. If the workbook has four worksheets and one chart sheet, wb.Sheets.Count returns 5. The macro then places that value in the message. A Long variable is appropriate for storing the count and is commonly used for collection counts in VBA.

Before running any macro, save a copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, even when a macro appears to make only a small change. This example only reads a count, but keeping a backup is a sound habit when running VBA code.

Sheets.Count versus Worksheets.Count

Excel exposes different collections for different kinds of sheet objects. The distinction matters when a workbook contains chart sheets or older sheet types, not just standard grid-based worksheets.

Sheets.Count counts the workbook’s sheets across sheet types, including worksheets and chart sheets.
Worksheets.Count counts only worksheet objects—the sheets with cells arranged in rows and columns.

For example, suppose a workbook contains three worksheets and two chart sheets. Sheets.Count returns 5, while Worksheets.Count returns 3. If a macro will loop through worksheet cells, Worksheets.Count is usually the relevant count. If it is checking the number of sheet tabs or trying to account for all sheet types, use Sheets.Count.

Use a qualified reference such as ThisWorkbook.Worksheets.Count rather than relying on an unqualified Worksheets.Count. Qualification makes the intended workbook explicit and prevents the result from depending on which workbook happens to be active.

Choose the workbook you actually want to count

A correct count can still be wrong for the task if the code points to the wrong workbook. VBA provides two common references:

ThisWorkbook is the workbook that contains the running VBA project. Use it when the macro is stored in the workbook whose sheets you want to count.
ActiveWorkbook is the workbook currently active in Excel. Use it when the macro should operate on whichever workbook the user has selected.

These references can differ. For example, if the macro is stored in the Personal Macro Workbook or an add-in, ThisWorkbook refers to that file, not necessarily to the document being edited. In that situation, the active workbook may be the intended target, but it should be selected deliberately. A macro that counts the wrong workbook may return a plausible number, so this issue is easy to miss.

For a workbook already assigned to a variable, use that variable. For example, after code has set wb to the desired workbook, read wb.Sheets.Count. This approach is especially useful in automation that opens or processes several workbooks because it avoids depending on what is active at a particular moment.

Count only worksheets or only visible worksheets

For a worksheet-only total, replace Sheets with Worksheets. This counts worksheets whether they are visible, hidden, or set to xlSheetVeryHidden. A hidden worksheet is still part of the workbook’s worksheet collection; hiding it does not remove it or make it stop contributing to the count.

If the requirement is to count visible worksheets only, loop through the worksheets and test each sheet’s Visible property:

Sub CountVisibleWorksheets()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim visibleCount As Long

    ' Count visible worksheets in the workbook containing this code.
    Set wb = ThisWorkbook
    For Each ws In wb.Worksheets
        If ws.Visible = xlSheetVisible Then
            visibleCount = visibleCount + 1
        End If
    Next ws

    MsgBox "Visible worksheets: " & visibleCount, vbInformation
End Sub

This loop deliberately uses Worksheets, so a visible chart sheet is not included. To count visible sheets of all types, the code would need to loop through wb.Sheets and check the visibility of each sheet object instead. Be clear about whether “visible sheets” means visible worksheets or every visible tab type.

Use the count in a macro

A count is often more useful as a value for a decision than as a message. For example, a macro can use the number to check whether a workbook has the expected number of worksheets before continuing:

Sub CheckWorksheetCount()
    Dim wb As Workbook
    Dim worksheetCount As Long

    ' Check the workbook containing this code.
    Set wb = ThisWorkbook
    worksheetCount = wb.Worksheets.Count

    If worksheetCount < 3 Then
        MsgBox "This workbook has fewer than three worksheets.", vbExclamation
        Exit Sub
    End If

    MsgBox "Worksheet count: " & worksheetCount, vbInformation
End Sub

The comparison is an example, not a rule about how many sheets a workbook should contain. In a real macro, choose the threshold based on the workbook’s purpose. Counting first can also help with loops: a loop from 1 through wb.Worksheets.Count can visit each worksheet by index, though a For Each loop is often simpler when the code needs to work with each worksheet object.

Use the result directly when it is only needed once. Store it in a variable when it will be reused, displayed, or compared. If other code adds or deletes sheets between two checks, calculate the count again rather than assuming an earlier value is still current.

Run the macro in Excel for Windows or Mac

In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the module, and run it with the cursor inside the procedure by pressing F5. You can also return to Excel and use Developer > Macros to select and run a macro. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon and select Developer.

Excel for Mac also supports VBA macros in the desktop application, although keyboard shortcuts and some interface details can vary by keyboard and Excel version. Use the Developer tab’s Visual Basic command to open the editor if the Windows shortcut is unavailable; the Mac keyboard equivalent may require Option+F11 or the Fn key as well. Excel for the web does not run VBA macros, so open the file in the desktop Excel application to use this code.

Macro-enabled files are commonly saved as Excel Macro-Enabled Workbook (.xlsm) files. If Excel displays a security warning, only enable macros in a file you trust. A macro that only counts sheets is read-only, but other code in a workbook may perform actions such as changing or deleting content.

Common counting mistakes and misunderstandings

The most frequent source of an unexpected result is counting a different collection or workbook from the one intended. Check these points before changing the code:

A chart sheet is included: This is expected for Sheets.Count, but not for Worksheets.Count.
A hidden sheet is included: Hiding a sheet does not remove it from the collection. Add a visibility test if only visible sheets should count.
The wrong workbook is referenced: Decide whether the target is ThisWorkbook, ActiveWorkbook, or a workbook variable.
A tab is mistaken for a worksheet: A chart sheet has a tab but does not contain the ordinary worksheet cell grid. A tab’s appearance alone does not establish its object type.
The code is run from the wrong place: A macro stored in an add-in or Personal Macro Workbook may not have the same ThisWorkbook as the open file being inspected.

Also distinguish a sheet count from a workbook-window count. A workbook can be displayed in more than one window, but those windows are not additional sheets. Similarly, counting worksheets does not tell you how many populated sheets there are: an empty worksheet still counts as one worksheet.

Alternative: use the SHEETS worksheet function

If you need a count displayed in a cell and do not need VBA to act on it, Excel has a worksheet function called SHEETS. In an otherwise empty cell such as A1, enter =SHEETS() to return the number of sheets in the workbook. With a reference supplied, the function counts sheets in that reference. The no-argument form is a convenient way to show the workbook-wide total in the grid.

The worksheet function is useful for a visible, automatically recalculated result, while VBA is more suitable when the count must control a macro, be written to a report, or be checked across a chosen workbook object. Like Sheets.Count, SHEETS counts sheet types rather than only worksheet tabs. If you need only worksheets or only visible worksheets, use the relevant VBA collection or visibility loop instead. The function is available in modern Excel versions, including Microsoft 365.

Frequently Asked Questions

Does Sheets.Count include hidden sheets?
Yes. Sheets.Count includes hidden sheets, and Worksheets.Count includes hidden worksheets. Test each sheet’s Visible property if the count should include visible sheets only.

What is the difference between Sheets.Count and Worksheets.Count?
Sheets.Count includes different sheet types, such as worksheets and chart sheets. Worksheets.Count includes worksheets only. Choose based on whether the task concerns every sheet type or only grid-based sheets.

How do I count sheets in the active workbook?
Use ActiveWorkbook.Sheets.Count when the active workbook is definitely the target. If your macro is stored in that same workbook, ThisWorkbook.Sheets.Count is generally more explicit and reliable.

Can I put the sheet count in a cell with VBA?
Yes. Assign wb.Sheets.Count to a variable and write the value to a chosen worksheet cell, such as wb.Worksheets(1).Range("A1").Value. Make sure the workbook and destination sheet are the ones you intend to change.

Can Excel for the web run this VBA macro?
No. Excel for the web can open and edit many workbook files, but it does not run VBA macros. Use the desktop Excel application for the macro, or use the SHEETS function when a cell-based count is sufficient.

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.