Listing Sheets in an workbook

[Fernando Gomez]

Happen that I have a file with about 120 sheets, I need to make a list in a
separate sheet with the name given to every sheet, I think it could be done
with macros, but I have no idea how to start, could somebody help me with
this.

Thanks in advance

 

[J.E. McGimpsey]

One way:

Public Sub ListSheets()
Dim vList As Variant
Dim i As Long
With ThisWorkbook.Sheets
ReDim vList(1 To .Count, 1 To 1)
For i = 1 To .Count
vList(i, 1) = Sheets(i).Name
Next i
With ThisWorkbook.Worksheets.Add
.Name = "Sheet List"
.Range("A1").Resize(UBound(vList, 1), 1).Value = _
vList
End With
End With
End Sub

Also, you may want to look at David McRitchie's BuildToc macro,
which builds a table of contents including hyperlinks to the sheets:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm

 

[David McRitchie]

Hi Fernando,

two solutions:

more extensive information:

Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

shorter solution, just the names
List names of sheets down from active cell (#sheetnaemsdownrows) in
Build TOC Another Approach
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm

In either case would suggest sorting your worksheets, macros are
included on either webpage. the big macro asks you if you want to
sort the worksheet tabs.

Also be aware that you can right click on the sheettab navigation arrows
to get a list of worksheet (more sheets) but you really want those tabs to
be sorted to use this for a large number of worksheets.

 

[J.E. McGimpsey]

One change needed to avoid a potential error. Replace

Sheets(i).Name

with

.Item(i).Name