Skip to content 
Excel Count Sheets ( by using VBA)
With Excel’s built-in functions and features, you can accomplish certain straightforward tasks using VBA that are not feasible.
One example of such a task is counting the total number of sheets in the open workbook or any other open workbook on your system.
In this article, I’ll demonstrate some basic VBA codes that you can use to get the total number of sheets in an Excel document.
This instruction explains:
1. Count all of the worksheet sheets. 1.1 Sheet Count Display in a Message Box Using VBA 1.2 Receiving Sheet Count Results in an Instant 1.3 Formula to Determine the Worksheet's Sheet Count 2. All Sheets in Another Workbook, Totaled (Open or Closed 2.1 In Another Open Workbook, Sheet Count 2.2 Count of Sheets in a Closed Workbook 3. Count the number of sheets that contain a certain word.
1. Count all of the worksheet sheets.
I have many options for using VBA to calculate the total number of sheets in a worksheet.
You can select one of the three techniques I demonstrate in the sections that follow:
- using a module’s VBA code (to get sheet count as a message box)
- Immediate window use
- Utilizing a Special Formula (which will give you the sheet count in a cell in the worksheet)
1.1 Sheet Count Display in a Message Box Using VBA
The VBA code to display the total number of sheets in the active workbook in a message box is provided below:
Sub SheetCount() MsgBox ThisWorkbook.Sheets.Count End Sub
I used Sheets, which will count all the sheets, in the code above (be it worksheets or chart sheets). Use Worksheets rather than Sheets if you simply want to count the total number of worksheets. Most of the time, individuals simply utilize spreadsheets, therefore utilizing Sheets is OK. Sheets are simply defined as Worksheets + Chart Sheets.
- The steps to insert this code into the VBA Backend are as follows:
- If you can’t find the Developer tab in the ribbon, click here to learn how to get it.
- On the Visual Basic icon, click
- Select the “Insert” option from the menu in the Visual Basic Editor that appears.
- Select Module. will insert a new module for the workbook
- Copy and paste the aforementioned VBA code into the module’s code window.
- Press the F5 key when the cursor is anywhere in the code (or click the green play button in the toolbar)
If you follow the instructions above, the code will execute and you will get a message box with a count of all the worksheets in the workbook.
Note: You must save the Excel file as a macro-enabled file if you intend to save this code and use it in the same workbook in the future (with a . XLSM extension). When you attempt to save this file, you will see this option.
1.2 Receiving Sheet Count Results in an Instant
With only one line of code, the Immediate window immediately shows you the outcome.
The procedures to obtain the workbook’s sheet count using the immediate window are as follows:
If you can’t find the Developer tab on the ribbon, go here to learn how to acquire it.
On the Visual Basic icon, click
You might already see the Immediate Window in the VB Editor when it launches. If you don’t, use the keyboard shortcut Control + G to choose Immediate Window from the menu’s ‘View’ option.
The following line of code should be copied and pasted:
ThisWorkbook.Sheets.Count
Hit enter with the cursor at the end of the code line.
When you press enter in Step 5, the code is run, and the result appears in the next line of the current window.
MS EXCEL VBA TUTORIAL
1.3 Formula to Determine the Worksheet’s Sheet Count
The formula approach is the greatest option if you want to find the number of sheets in a cell of any worksheet.
To calculate the total number of pages in the workbook using this technique, I will build a special formula.
code to accomplish this is shown below:
Function SheetCount() SheetCount = ThisWorkbook.Sheets.Count End Function
You must insert this code into the module (exactly as I demonstrated in the section titled “VBA Code to Show Sheet Count in a Message Box”)
Once the code is in the module, you can use the following formula in any workbook cell to get the number of sheets:
=SheetCount()
Pro Tip: I would suggest copying and pasting this formula VBA code into the Personal Macro Workbook if you frequently need to obtain the sheet count value. Any Excel file on your computer can be utilized with a VBA code that has been saved in the Personal Macro Workbook. In summary, you always have access to VBA codes on your system while using PMW.
2. All Sheets in Another Workbook, Totaled (Open or Closed)
I demonstrated how to count the number of sheets in the active workbook—the one in which you are now working and where the VBA scripts were added—in the example above.
You may also slightly modify the code to obtain the number of sheets in different spreadsheets (whether open or closed).
MS EXCEL VBA
2. 1 In Another Open Workbook, Sheet Count
Let’s say I want to know how many sheets are in the workbook book1.xlsx, which is currently open.
This is what the VBA code below does:
Sub Sheet_Count()
MsgBox Workbooks("book1.xlsx").Sheets.Count
End Sub Additionally, you may use the code below if you want to see the results right now.
? Workbooks("book1.xlsx").Sheets.Count MS EXCEL VBA
2.2 Count of Sheets in a Closed Workbook
If you want to know how many sheets are in a closed file, you may either open it and then use the aforementioned scripts, or you can use VBA to open the file, count its sheets, and then shut it.
Obtaining the Excel file’s complete file location would be the first step. My file is located at “C:UserssumitOneDriveDesktopTestExample File.xlsx” in this example.
By selecting the Copy Path option from the context menu when you right-click the file, you may obtain the whole file path (or click on the Properties option).
The VBA scripts to get the sheet count from the closed workbook is as follows:
Sub SheetCount()
Application.DisplayAlerts = False
Set wb = Workbooks.Open("C:\Users\sumit\OneDrive\Desktop\Test\Example File.xlsx")
ShCount = wb.Sheets.Count
wb.Close SaveChanges:=True
Application.DisplayAlerts = True
MsgBox ShCount
End Sub The code mentioned above opens the workbook first, counts all of its sheets, and then closes it.
The code has several more lines of code since it needs to perform certain additional activities in addition to counting the sheets.
The Program.
The code ensures that the user is not made aware of the process of opening a file, counting the sheets, and then shutting the file by setting Display Alerts = False. This line disables the Excel application’s alarms. In order to restore things to normal and see the alerts moving forward, we set it back to True at the conclusion of the code.
Count the number of sheets that contain a certain word.
When there are several pages in a workbook and you simply want to count the number of sheets that contain a particular term, counting sheets might be handy.
For instance, let’s say you just need to know the number of pages in the sales department’s workbook, despite the fact that your company’s enormous workbook has sheets for all of its divisions.
The VBA code for counting only the sheets with the term “Sales” on them is provided below.
Sub Sheet_Count() Dim shtCount As Long For Each sh In ThisWorkbook.Worksheets If InStr(1, sh.Name, "Sale", vbBinaryCompare) > 0 Then shtCount = shtCount + 1 End If Next shtCount MsgBox shtCount End Sub
The code is case-sensitive, thus the words “Sales” and “sales” would be interpreted differently.
The aforementioned checks each sheet’s name using an IF condition to see if it includes the requested word (which is verified using the INSTR function); otherwise, it does not.
These straightforward VBA routines may be used to rapidly determine the number of sheets in any worksheet.
MS EXCEL VBA TUTORIAL
I sincerely hope this instruction was helpful.
