DISPLAY SHEET NAMES

[bylar]

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.

 

[EGT01]

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

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

 

[yogia]

Hi bylar:

You may also want to try ...

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