Counting the total number of sheets in the active workbook or any other workbook on your system is an example of such a task. In this tutorial, I’ll show you some simple VBA code that you can use to count the total number of sheets in an Excel workbook.
Count All Sheets in the Workbook
There are multiple ways I can use VBA to give me the total count of sheets in a workbook. In the following sections, I will show you three different methods, and you can choose which one to use:
VBA Code to Show Sheet Count in a Message Box
Below is the VBA code to get the total number of sheets in the current workbook shown in a message box: In the above code, I have used Sheets, which will count all the sheets (be it worksheets or chart sheets). In case you only want to count the total number of worksheets, use Worksheets instead of Sheets. In most cases, people only use worksheets, using Sheets works fine. In layman terms, Sheets = Worksheets + Chart Sheets Below are the steps to put this code in the VBA Backend: The above steps would run the code and you will see a message box that shows the total number of worksheets in the workbook. Note: If you want to keep this code and reuse it in the future in the same workbook, you need to save the Excel file as a macro-enabled file (with a .XLSM extension). You will see this option when you try to save this file.
Getting Sheet Count Result in Immediate Window
The Immediate window gives you the result right there with a single line of code. Below are the steps to get the count of sheets in the workbook using the immediate window: When you hit enter in Step 5, it executes the code and you get the result in the next line in the immediate window itself.
Formula to Get Sheet Count in the Worksheet
If you want to get the sheet count in a cell in any worksheet, using the formula method is the best way. In this method, I will create a custom formula that will give me the total number of sheets in the workbook. Below is the code that will do this: You need to place this code in the module (just like the way I showed in the ” VBA Code to Show Sheet Count in a Message Box” section) Once you have the code in the module, you can get the sheet count by using the below formula in any cell in the workbook: Pro Tip: If you need to get the sheet count value often, I would recommend copying and pasting this formula VBA code in the Personal Macro Workbook. When you save a VBA code in the Personal Macro Workbook, it can be used on any Excel file on your system. In short, VBA codes in PMW are always available to you on your system.
Count All Sheets in Another Workbook (Open or Closed)
In the above example, I showed you how to count the number of sheets in the active workbook (the one where you’re working and where you added the VBA codes). You can also tweak the code a little to get the sheet count in other workbooks (whether open or closed).
Sheet Count in Another Open Workbook
Suppose I want to know the sheet count of an already open workbook – Example.xlsx The below VBA code with do this: And in case you want to get the result in the immediate window, you can use the below code.
Sheet Count from a Closed Workbook
In case you need to get the sheet count of a file that’s closed, you either need to open it and then use the above codes, or you can use VBA to first open the file, then count the sheets in it, and then close it. The first step would be to get the full file location of the Excel file. In this example, my file location is “C:\Users\sumit\OneDrive\Desktop\Test\Example File.xlsx” You can get the full file path by right-clicking on the file and then clicking on the Copy Path option (or click on the Properties option). Below are the VBA codes to get the sheet count from the closed workbook: The above code first opens the workbook, then counts the total number of sheets in it, and then closes the workbook. Since the code needs to do some additional tasks (apart from counting the sheets), it has some extra lines of code in it. The Application.DisplayAlerts = False part of the code makes sure that the process of opening a file, counting the sheets, and then closing the file is not visible to the user. This line stops the alerts of the Excel application. And at the end of the code, we set it back to True, so things get back to normal and we see the alerts from thereon.
Count All Sheets that Have a Specific Word In It
One useful scenario of counting sheets would be when you have a lot of sheets in a workbook, and you only want to count the number of sheets that have a specific word in them. For example, suppose you have a large workbook that has sheets for multiple departments in your company, and you only want to know the sheet count of the sales department sheets. Below is the VBA code that will only give you the number of sheets that have the word ‘Sales’ in it Note that the code is case-sensitive, so ‘Sales’ and ‘sales’ would be considered different words. The above uses an IF condition to check the name of each sheet, and if the name of the sheet contains the specified word (which is checked using the INSTR function), it counts it, else it doesn’t. So these are some simple VBA codes that you can use to quickly get a count of sheets in any workbook. I hope you found this tutorial useful! Other Excel tutorials you may also find useful:
How to Insert New Worksheet in Excel (Easy Shortcuts)How to Group Worksheets in ExcelHow to Get the Sheet Name in Excel? Easy FormulaHow to Sort Worksheets in Excel using VBA (alphabetically)How to Delete Sheets in Excel (Shortcuts + VBA)How to Rename a Sheet in Excel (4 Easy Ways + Shortcut)Excel Tabs/Sheets Not Showing – How to Fix?
