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