How to COUNT Sheets using VBA in Excel

In Excel, if you have many sheets, you can use a VBA code to count them quickly instead of manually counting or using any formula. So, in the post, we will see different ways to do count sheets from a workbook.

Count Sheets from the Active Workbook

Following is the code that you need to use to count the sheet from the active workbook.

ThisWorkbook.Sheets.Count

In this code, first, you have the referred to the active workbook using the “ThisWorkbook” and refer to all the sheet, in the end, use the count method to count all the sheets. And if you want to count the worksheets instead of sheets, then use the following code.

ThisWorkbook.Worksheets.Count

Helpful Links: Run a Macro – Macro Recorder – Visual Basic Editor – Personal Macro Workbook

Count Sheets from a Different Workbook

You can use the name of the workbook to refer to and then count the sheets from it. Let’s say you want to count the sheets from the workbook “Book1”.

Here’s the code.

Workbooks("sample-file.xlsx").Sheets.Count

This code gives you the count of the sheets that you have in the workbook “sample-file.xlsx“. There is one thing that you need to take this workbook needs to be open.

Count Sheets from All the Open Workbooks

You might have more than one workbook that is open at the same time, and you can count all the sheets from all those workbooks.

Sub vba_loop_all_sheets()

Dim wb As Workbook
Dim i As Long

For Each wb In Application.Workbooks

  If wb.Name <> "PERSONAL.XLSB" Then

      i = i + wb.Sheets.Count

  End If

Next wb

MsgBox "Total sheets in all the open workbooks: " & i

End Sub

Count Sheets from a Closed Workbook

Now here we have a code that refers to the workbook that is saved on my system’s desktop. When I run this code it opens that workbook at the backend and counts the sheets from it and then adds that count to cell A1.

Sub vba_count_sheets()

Dim wb As Workbook

Application.DisplayAlerts = False

Set wb = Workbooks.Open("C:UsersDellDesktopsample-file.xlsx")

ThisWorkbook.Sheets(1).Range("A1").Value _
= wb.Sheets.Count

wb.Close SaveChanges:=True

Application.DisplayAlerts = True

End Sub

We have turned OFF the display alerts to open and close the file at the backend.