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.CountEnd 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:UsersBobDesktopmy_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:UsersBobDesktopmy_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:UsersBobDesktopmy_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.