Monday, 13 February 2017

Find the total number of sheets in an Excel Workbook

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.

  1. Goto VBE, by pressing ALT + F11 or Click on VisualBasic Icon from the Develepor Tab.
  2. Activate Immediate window by Pressing CTRL + G
  3. ?activeworkbook.Sheets.Count   Copy paste the highlighted VBA code to immediate window and press Enter
  4. 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.