How can I generate a list of the worksheets by name

[georgia-miner]

How can I generate a list by name of the worksheets in a large workbook? I
have a workbook that contains over 100 large spreadsheets. Each worksheet
contains detailed information on a specific property. I have been trying to
figure out how to generate a list of the worksheets so that I can show which
properties are captured in this workbook. I do not want to go to Access. I
do not have time to design a database that gives me the information that I
can access now in seconds using a summary sheet. The list I need is for
management reports.
Thanks, Connie

 

[Gary''s Student]

First enter this tiny UDF:

Function sheetname(i As Integer) As String
sheetname = Sheets(i).Name
End Function

then in row 1 of any column enter:

=sheetname(ROW())


and copy down.

 

[Gord Dibben]

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP