VBA Code to Get the count of all sheets in an Excel Workbook
Sometime I come across with Excel reports with numerous worksheets / tabs.
Sometimes you can't find the starting or ending of the workbook.
Whenever I see those kind of Excel reports, I always wonder how many sheets will be there in that workbook !
Of Course, You can count the Excel sheets one by one. Select one sheet, then press CTRL + Tab to goto next sheet, keep on counting in your head. :) Very good time pass.
Excel is not providing a way to find how many worksheets are existing in a workbook.
If you want to know the count of all sheets in an Excel workbook, we have an easy way.
- Goto VBE, by pressing ALT + F11 or Click on VisualBasic Icon from the Develepor Tab.
- Activate Immediate window by Pressing CTRL + G
- ?activeworkbook.Sheets.Count Copy paste the highlighted VBA code to immediate window and press Enter
- You can see the count of the sheets in the active Excel workbook has printed just below the code which you have entered.
msgbox activeworkbook.Sheets.Count If you use this code, a message box will be shown with count of sheets in the workbook.
This code becomes very important when you write VBA routines and loops.