×
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 SHEET NAMES

DISPLAY SHEET NAMES

DISPLAY SHEET NAMES

(OP)
Is there a way to display the names of all the sheets in a sheet.
I,ve got alot of sheets in a file and would like to see all the names displayed in one of the sheets.

RE: DISPLAY SHEET NAMES

bylar,

I have used the following VBA code to generate and/or update a separate Table of Contents (TOC) worksheet.  I am by no means a VBA expert but give this a try.

It has been some time since I used this but I did test it before posting here so I think you should be able to copy the following just as it is and put it in the ThisWorkbook section and get it to work.

Run macro TOC    

CODE

Sub TOC()
Dim ws As Worksheet

'Check each worksheet name to see if it is "TOC".
'If not, then create a new worksheet named "TOC"
'and place at the beginning of the workbook.
For Each ws In Worksheets
    If ws.Name = "TOC" Then
        GoTo Line1
    End If
    Next
    With Worksheets.Add
        .Name = "TOC"
        .Move before:=Worksheets(1)
    End With

'On worksheet named "TOC", first go to cell A3 and
'clear all cells to the end, then beginning at cell
'A3, start listing all the names of the worksheets
'in the workbook advancing the cursor one cell down.
Line1:
    
    Worksheets("TOC").Select
    Worksheets("TOC").Range("A3").Select
    Worksheets("TOC").Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Selection.ClearContents
    Worksheets("TOC").Range("a3").Activate
    
    For Each ws In Worksheets
        If ws.Name <> "Sheet Names" Then
            ActiveCell.Formula = ws.Name
            ActiveCell.Offset(1, 0).Select
    End If
    Next

End Sub

RE: DISPLAY SHEET NAMES


Hi bylar:

You may also want to try ...

CODE

Sub yListAllSheetNamesInWorkbook()
    For i = 1 To Sheets.Count
        cells(i, 1) = Sheets(i).Name
    Next i
End Sub
This will print the name of every sheet in the Workbook in column A of the ActiveSheet starting with row 1.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

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