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.
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
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
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
For i = 1 To Sheets.Count
cells(i, 1) = Sheets(i).Name
Next i
End Sub
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com