How to Count Number of Sheets in Excel VBA?

Excel 1k Views 1 Min Read

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
Tags: