Count the number of worksheets in a workbook
 

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!

with VBA

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

--

HTH

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


"Vincdc" wrote in message
...
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!

On Mon, 17 Jan 2005 09:07:03 -0800, Vincdc
wrote:

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

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


"Bob Phillips" wrote:

with VBA

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

--

HTH

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


"Vincdc" wrote in message
...
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!

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


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


"Bob Phillips" wrote:

with VBA

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

--

HTH

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


"Vincdc" wrote in message
...
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!

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)


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


"Bob Phillips" wrote:

with VBA

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

--

HTH

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


"Vincdc" wrote in message
...
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!

Really thanks for all your help! :=)

"Ron Rosenfeld" wrote:

On Mon, 17 Jan 2005 09:07:03 -0800, Vincdc
wrote:

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

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.

Vincdc wrote:

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!

--

Dave Peterson