Count Worksheets (97 & 2000)

[cshepard]

How can I easily find out how many worksheets are in a workbook?

[JohnBF]

Open the VBE and in the Immediates window enter

?ThisWorkbook.Worksheets.Count

and press enter.

I was looking for a formula, but =COUNTBLANK() isn't 3D.

[LegareColeman]

The following User Defined Function will return the number of worksheets in the activeworkbook (does not include other kinds of sheets like chart sheets). However, Excel does not consider inserting a new worksheet a recalculate event. Therefore, the count will not update after inserting a new worksheet until some other change is made that causes a recalculate event.


<pre>Public Function NumSheets() As Integer
Application.Volatile
NumSheets = ActiveWorkbook.Worksheets.Count
End Function
</pre>

[kieran]

This thread may be of use..

[rory]

Just as an alternative to the other suggestions, choose Insert-Name-Define, choose a name (e.g. SheetCount) and in the refers to box type:
=GET.WORKBOOK(4)
then in any cell in your workbook you can use =sheetcount.
Note that this has the same drawback that Legare mentioned with his VBA solution.