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