count worksheets in a workbook in Excel

I want to count the number of worksheets contaned in an Excel workbook.  How can I do this/
Dier02Asked:
Who is Participating?
 
mscanlon06851Connect With a Mentor Commented:
It's not clear how you want to use the information.  If it's within a VBA macro, then

    thisworkbook.sheets.count

will give you what you want.

Or, if you want the sheet count from an open workbook other than the activeworkbook:

   workbooks("xxxxxxx.xls").sheets.count

  ( where xxxxxxx.xls = the open workbook's name)

will return the number you're looking for.

If for some reason you want the number of sheets in the workbook to appear in a worksheet cell, you can create a user-defined function that will do the job:

- Using the Visual Basic Editor, add the following code to a module:

      Function SheetsInWorkbook() As Integer
          SheetsInWorkbook = ThisWorkbook.Sheets.Count
     End Function

- save the workbook

- in a cell in your workbook, enter:
     
     =SheetsInWorkbook()

the number of sheets in the workbook will appear in that cell.



0
 
loopfinityConnect With a Mentor Commented:
try

wkbkobjectname.Worksheets.count
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.