A more robust solution, in case the user has multiple workbooks
open, would be the following:

Public Function GetSheetCount() As Long
GetSheetCount =
Application.Caller.Parent.Parent.Worksheets.Count
End Function


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




"Fredrik Wahlgren" wrote
in message ...
If you want a function, you have to roll your own. Select
Tools|Visual basic
Editor... insert a new module and paste the code below

Public Function GetSheetCount() As Long
GetSheetCount= ActiveWorkbook.Worksheets.Count
End Function

You can now enter =GetSheetCount() in a cell and it will return
the # of
sheets in your workbook

/Fredrik

"Steve R" wrote in
message
...
How about:

Sub NumSheets()

TotSheets = Worksheets.Count

i = 1
For Each sh In Worksheets
sh.Name = i & " of " & TotSheets
i = i + 1
Next sh

End Sub


"carlos sosa" <carlos wrote in
message
...
excel doesn't have a function to count sheets in a book. i
think it is
useful
when a result depends on the number of sheets you have in a
book. for
example, i would like to number the pages 1 of 3, 2 of 3 and
3 of 3
automaticaly and it is imposible if excel is no able to
count sheets
unless
you use a macro with visual basic.