Counting Number of Worksheets in Workbook

[John Barr]

I want to setup a cell in Excel that counts the number of
worksheets in the workbook. Can anyone help me with how to
do this? All I see are worksheet functions.

 

[Paul Corrado]

John,

If you have data in the same cell in all of the worksheets, such as title
information that always starts in A1, you can use

COUNTA(Sheet1:Sheet3!A1)

And that figure would be the number of worksheets.

HTH

PC

 

[Ron de Bruin]

Your sheetnames are different then I think
But this will work

=SUM(start:end!C1)

Put in two dummy sheets(empty sheets)with the name start as the first sheet
and one with the name end as the last one.
this are empty sheets!!!

Te sheets in between will be sum

 

[mlindsay]

I suggest using a VBA user defined function like:

Function cnt_sheets() As Integer
cnt_sheets = Sheets.Count
End Function

then simple entering "=cnt_sheets()" into a cell will give you your results.

--
Michael R. Lindsay NCT
(e-mail address removed)
(An old dog still trying
to learn new tricks...)

(Do you remember:
2 MHz 8-Bit CPU?s
64K maximum Ram
300 Baud Acoustic Couplers
Data storage on Audio Cassette tapes)

 

[John Barr]

Thank you. It worked just fine.

-----Original Message-----
I suggest using a VBA user defined function like:

Function cnt_sheets() As Integer
cnt_sheets = Sheets.Count
End Function

then simple entering "=cnt_sheets()" into a cell will give you your results.

--
Michael R. Lindsay NCT
(e-mail address removed)
(An old dog still trying
to learn new tricks...)

(Do you remember:
2 MHz 8-Bit CPU?s
64K maximum Ram
300 Baud Acoustic Couplers
Data storage on Audio Cassette tapes)





.

 

[Ron de Bruin]

I really need a vacation and some new glasses <g>

 

[Dana DeLouis]

This counts "All" worksheets. It would only be accurate with one workbook
open. If not, maybe you could adjust it.

=INFO("numfile")

 

[Harlan Grove]

This counts "All" worksheets. It would only be accurate with one workbook
open. If not, maybe you could adjust it.

=INFO("numfile")

...

If the OP loads Personal.XLS, this would include the worksheets in it. Maybe
best to term this contingently useful.