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.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

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