excel should have a function to count sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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]

?
 
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
 
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
 
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?
 
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.
 
That was about the sheets. I need including the page breaks inside each sheet. Need to assing the values based on the page number. for E.g. there may be 5 sheets but print out may come out for 10 pages. I need the function clause like the earlier for the same as it is part of processing and not only prinouts. Printouts can be handeled in footer.
 
Back
Top