Create a list of pre-named tabs

[darsh]

Hi,

Wondering how to create a list of pre-existing tabs in a workbook. I have a
book that has appx 100 tabs and I want to create a list of those tabs on one
sheet.

Is there an easier way then doing this manually? The tab names keep changing
every month and I need to get a quick inventory of what's in the book.

Many Thanks,

 

[FSt1]

hi
Sub sheetnamesonSheet()
Dim r As Range
Dim rd As Range
Dim WS As Worksheet
Set r = Range("A2") 'change if needed. used sheet1 for demo
For Each WS In Worksheets
Set rd = r.Offset(1, 0)
r.Value = WS.Name
Set r = rd
Next WS
End Sub

goes in a standard module.

regards
FSt1

 

[Per Jessen]

Hi

This will write all sheet names to a sheet named 'Name Sheet', except
'Name Sheet'

Sub NameList()
Dim DestSh As Worksheet
Dim DestCell As Range

Set DestSh = Worksheets("Name Sheet") 'Change to suit
Set DestCell = DestSh.Range("A1")

Range(DestCell, DestCell.End(xlDown)).ClearContents

For Each sh In ThisWorkbook.Sheets
If sh.Name <> DestSh.Name Then
DestCell = sh.Name
Set DestCell = DestCell.Offset(1, 0)
End If
Next
End Sub

Regards,
Per