How to Count Number of Sheets in Excel VBA?

In this post, you’ll learn how to count numbers of sheets in a Excel Workbook using Excel VBA.

To run the Excel VBA, You will need do the following first,

  • Under the Developer tab, Click Visual basics
  • Click on the insert option and click the module
  • Enter the codes and run it.

Count Number of Sheets in Excel VBA

To count the total number of sheets in a workbook using the Excel VBA command:



Put this in a module:

Public Sub CountSheets()
MsgBox Application.Sheets.Count
End Sub
How to Count Number of Sheets in Excel VBA?

Count Sheets from the Active Workbook

To count the sheet from the active workbook:



To count the worksheets instead of sheets, then use the following code.



Count Sheets from a Different Workbook

To count sheets from a different Workbook(excel file)


Workbooks("Name of the file.xlsx").Sheets.Count

Count Sheets from All the Open Workbooks

To count sheets from all the open workbooks


Sub vba_loop_all_sheets()

Dim wb As Workbook

Dim i As Long

For Each wb In Application.Workbooks

  If wb.Name <> "Book1.XLSB" Then

      i = i + wb.Sheets.Count

  End If

Next wb

MsgBox "Total sheets in all the open workbooks: " & i

End Sub