You can use the following methods to count the number of sheets in a workbook in Excel:
Method 1: Count Number of Sheets in Active Workbook
Sub CountSheetsActive()
Range("A1") = ThisWorkbook.Worksheets.Count
End Sub
Method 2: Count Number of Sheets in Open Workbook
Sub CountSheetsOpen()
Range("A1") = Workbooks("my_data.xlsx").Sheets.Count
End Sub
Method 3: Count Number of Sheets in Closed Workbook
Sub CountSheetsClosed()
Application.DisplayAlerts = False
Set wb = Workbooks.Open("C:\Users\Bob\Desktop\my_data.xlsx")
'count sheets in closed workbook and display count in cell A1 of current workbook
ThisWorkbook.Sheets(1).Range("A1").Value = wb.Sheets.Count
wb.Close SaveChanges:=True
Application.DisplayAlerts = True
End Sub
The following examples show how to use each of these methods in practice.
Example 1: Count Number of Sheets in Active Workbook
Suppose we have the following Excel workbook open and we’re viewing it:
We can use the following macro to count the total number of sheets in this workbook and display the count in cell A1:
Sub CountSheetsActive()
Range("A1") = ThisWorkbook.Worksheets.Count
End Sub
When we run this macro, we receive the following output:
Notice that cell A1 contains a value of 6.
This tells us that the there are 6 sheets in this workbook.
Example 2: Count Number of Sheets in Open Workbook
Suppose we have an Excel workbook called my_data.xlsx with two sheets that is opened but we’re not currently viewing it.
We can use the following macro to count the total number of sheets in this workbook and display the count in cell A1 of the active workbook:
Sub CountSheetsOpen()
Range("A1") = Workbooks("my_data.xlsx").Sheets.Count
End Sub
When we run this macro, we receive the following output:
Notice that cell A1 contains a value of 2.
This tells us that the there are 2 sheets in the open workbook called my_data.xlsx.
Example 3: Count Number of Sheets in Closed Workbook
Suppose we have an Excel workbook called my_data.xlsx with two sheets that is not currently open but is located in the following file location:
C:\Users\Bob\Desktop\my_data.xlsx
We can use the following macro to count the total number of sheets in this workbook and display the count in cell A1 of the first sheet of the active workbook:
Sub CountSheetsClosed()
Application.DisplayAlerts = False
Set wb = Workbooks.Open("C:\Users\Bob\Desktop\my_data.xlsx")
'count sheets in closed workbook and display count in cell A1 of current workbook
ThisWorkbook.Sheets(1).Range("A1").Value = wb.Sheets.Count
wb.Close SaveChanges:=True
Application.DisplayAlerts = True
End Sub
When we run this macro, we receive the following output:
Notice that cell A1 contains a value of 2.
This tells us that the there are 2 sheets in the closed workbook called my_data.xlsx.
Note: Within the code, Application.DisplayAlerts=False tells VBA not to display the process of opening the closed workbook, counting the sheets, and then closing the workbook.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Rows in Range
VBA: How to Count Cells with Specific Text
VBA: How to Write COUNTIF and COUNTIFS Functions