excel should have a function to count sheets
 

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.

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.

Hi

you could also use the Report Manager add-in (ver 2000 tools / addins / tick
report manager and then you'll find it under the insert menu) with ver
2002/2003 check out MS's site for it ... if you can't find it post back and
i'll dig out the web page address

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"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.

carlos sosa wrote...
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.

Uh . . . why not use Page Setup headers or footers containing

&[Page] of &[Pages]

?

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.

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.

Did you ever happen to get a full answer to this? I'm having the same issue.
The reply that allows you to create the insertable "getsheetcount" function
works for the total pages issue, but what about the "1 of x", "2 of x" issue?
Is there a macro to create a function in a cell to identify the sheet you're
on, insert the "of" in between, and then show the total number of sheets?

"carlos sosa" wrote:

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.

Maybe I'm missing something, but you can do this very easily in a footer.
It's somewhat complicated (and not foolproof) to do it using a formula in a
cell.

--

Vasant

"Brandon" wrote in message
...
Did you ever happen to get a full answer to this? I'm having the same
issue.
The reply that allows you to create the insertable "getsheetcount"
function
works for the total pages issue, but what about the "1 of x", "2 of x"
issue?
Is there a macro to create a function in a cell to identify the sheet
you're
on, insert the "of" in between, and then show the total number of sheets?

"carlos sosa" wrote:

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.