In Excel, if you have many sheets, you can use a VBA code to count them quickly instead of manual 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.
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.
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.
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 that 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 the add that count to the cell A1.
Sub vba_count_sheets() Dim wb As Workbook Application.DisplayAlerts = False Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-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.
More Tutorials on Worksheets
- VBA Worksheets – Understanding VBA Worksheet Object
- How to LOOP Through All the Sheets in a Workbook using VBA
- How to CHECK IF a Sheet Exists in a Workbook using VBA
- How to CLEAR an Entire Sheet in Excel using VBA
- How to PROTECT and Unprotect a Sheet in Excel using VBA
- How to DELETE a SHEET using VBA
- How to HIDE\UNHIDE a Sheet in Excel using VBA
- How to RENAME a Sheet in Excel using VBA
- How to COPY a Sheet in Excel using VBA
- How to ACTIVATE a Sheet using VBA
- How to ADD a Sheet in Excel using VBA