Display the number of sheets in a workbook 1

[Clyde38]

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?

 

[joerd]

Pretty straightforward:

Function SheetTabName(r As Range)
    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 for tips on how to make the best use of Eng-Tips.

 

[Clyde38]

Joerd,
Thanks!!