Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1188
  • Last Modified:

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/
0
Dier02
Asked:
Dier02
2 Solutions
 
loopfinityCommented:
try

wkbkobjectname.Worksheets.count
0
 
mscanlon06851Commented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now