excel should have a function to count sheets

C

carlos sosa

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

Steve R

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
 
J

JulieD

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
 
H

Harlan Grove

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
Click to expand...
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.
Click to expand...

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

&[Page] of &[Pages]

?
 
F

Fredrik Wahlgren

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
 
C

Chip Pearson

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
 
B

Brandon

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?
 
V

Vasant Nanavati

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