Macro to Count the Worksheets in a Workbook

These VBA macros count the worksheets in a workbook: all of them, only the visible ones, or every sheet including chart sheets.

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

Count all worksheets

Sub CountSheets()
    MsgBox "This workbook has " & Worksheets.Count & " worksheets."
End Sub

Count only visible sheets

Sub CountVisibleSheets()
    Dim ws As Worksheet, n As Long
    For Each ws In Worksheets
        If ws.Visible = xlSheetVisible Then n = n + 1
    Next ws
    MsgBox n & " of " & Worksheets.Count & " worksheets are visible."
End Sub

Count without a macro

Excel 2013 and later have a formula for this:

=SHEETS()

=SHEETS() counts all sheets in the workbook, including hidden and chart sheets. =SHEET() returns the number of the current sheet.

Things to know

• Worksheets.Count counts normal worksheets. Sheets.Count also includes chart sheets.
• Hidden and very hidden sheets are included in both counts.
• Use the count in other macros, for example to add a sheet at the end: Worksheets.Add After:=Worksheets(Worksheets.Count).

Related macros