×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Display the number of sheets in a workbook

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

RE: Display the number of sheets in a workbook

Pretty straightforward:

CODE

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

(OP)
Joerd,
Thanks!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources