Counting Number of Worksheets in Workbook

  • Thread starter Thread starter John Barr
  • Start date Start date
J

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.
 
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
 
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
 
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)
 
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)





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

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

=INFO("numfile")
Click to expand...
...

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