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:

Code:

Application.Sheets.Count

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:

Code

ThisWorkbook.Sheets.Count

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

Code

ThisWorkbook.Worksheets.Count

Count Sheets from a Different Workbook

To count sheets from a different Workbook(excel file)

Code

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

Count Sheets from All the Open Workbooks

To count sheets from all the open workbooks

Code

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
Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.