Display the number of sheets in a workbook
Display the number of sheets in a workbook
(OP)
On occasion I have workbooks that will continue to have worksheets added to the workbook. I would like to have a formula to display on each sheet something like the following:
Sheet # X of X Sheets
I know that if I use a sub such as:
Public Sub CountMySheets()
MsgBox Application.Sheets.Count
End Sub
I can display a message with the number of sheets. I don’t want to display a message, I want to be able to have a cell on each sheet display this. Any ideas?
Sheet # X of X Sheets
I know that if I use a sub such as:
Public Sub CountMySheets()
MsgBox Application.Sheets.Count
End Sub
I can display a message with the number of sheets. I don’t want to display a message, I want to be able to have a cell on each sheet display this. Any ideas?





RE: Display the number of sheets in a workbook
CODE
Application.Volatile
SheetTabName = r.Worksheet.Name
End Function
Function SheetIndex(r As Range)
Application.Volatile
SheetIndex = r.Worksheet.Index
End Function
Function SheetCount(r As Range)
Application.Volatile
SheetCount = r.Worksheet.Parent.Sheets.Count
End Function
Call 'em from your worksheet as
=SheetTabName(A1)
=SheetIndex(A1)
=SheetCount(A1)
The Application.Volatile statement will ensure recalculation of the formula every time something changes (such as the number of sheets), otherwise the formula will only update if the contents of cell A1 change.
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Display the number of sheets in a workbook
Thanks!!