Count the number of worksheets in a workbook

[Guest]

Hello:
Is there any function or other method to count the number of worksheets in a
workbook? I have a workbook which includes dozens of worksheets (each for one
client) and I would like to know the total number of clients.
Thanks in advance!

 

[Bob Phillips]

with VBA

Function WSNum()
WSNum = Activeworkbook.Worksheets.Count
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

Hello:
Is there any function or other method to count the number of worksheets in a
workbook? I have a workbook which includes dozens of worksheets (each for one
client) and I would like to know the total number of clients.
Thanks in advance!

A VBA UDF (User Defined Function) would be best.

It is possible to use a Named formula GET.WORKBOOK(4) but this can cause
problems resulting in Excel crashing under certain circumstances.

If you are only counting Worksheets, as you write, then:

============================
Function SheetCount()
SheetCount = ActiveWorkbook.Worksheets.Count
End Function
============================

However, be aware the Charts are not Worksheets. So if you have Chart sheets
that you wish to include in the count, use:

=========================
Function SheetCount()
SheetCount = ActiveWorkbook.Sheets.Count
End Function
=======================

To enter this UDF, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer Window, then
Insert/Module and paste the code into the window that opens.

In some cell in the workbook, enter the formula

=SheetCount()

The cell should display the count.


--ron

 

[Guest]

Thanks!
Just be curious, is there any built-in formula or function for this one? I
mean not the VBA code.

 

[Chip Pearson]

There is no built-in function to do this. You must use VBA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

You could create a workbook name, say NumSheets, with a refers to value of
=GET.WORKBOOK(4) , and then use =NumSheets in the worksheet. But be warned,
if you copy this cell in any Excel version prior to XL2002, it causes an
Excel crash. so I advise against it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Really thanks for all your help! :=)

A VBA UDF (User Defined Function) would be best.

It is possible to use a Named formula GET.WORKBOOK(4) but this can cause
problems resulting in Excel crashing under certain circumstances.

If you are only counting Worksheets, as you write, then:

============================
Function SheetCount()
SheetCount = ActiveWorkbook.Worksheets.Count
End Function
============================

However, be aware the Charts are not Worksheets. So if you have Chart sheets
that you wish to include in the count, use:

=========================
Function SheetCount()
SheetCount = ActiveWorkbook.Sheets.Count
End Function
=======================

To enter this UDF, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer Window, then
Insert/Module and paste the code into the window that opens.

In some cell in the workbook, enter the formula

=SheetCount()

The cell should display the count.


--ron

 

[Dave Peterson]

I'm not sure if it's important, but this version will calculate the number of
worksheets in the workbook with the formula.


Option Explicit
Function WSNum()
WSNum = Application.Caller.Parent.Parent.Worksheets.Count
End Function

Application.caller is the cell containing the formula.
its parent is the worksheet.
the worksheet's parent is the workbook.

If you open two workbooks (each with a different number of worksheets in them)
and use window|arrange|horizontal.

You should be able to notice the difference.