Create list of tabs in Excel

I'm working on a spreadsheet with a lot of tabs. Is there a simple way to generate a list of the tab names?

Thanks.
 

Question Info


Last updated April 2, 2020 Views 88,930 Applies to:
Answer
Answer

You can do this only with VBA code. There is no worksheet function to get sheet names.

 

Sub ListSheetNames()
    Dim R As Range
    Dim WS As Worksheet
    Set R = ActiveCell
    For Each WS In ThisWorkbook.Worksheets
        R.Value = WS.Name
        Set R = R(2, 1)
    Next WS
End Sub

Select the cell where the list should start and run the code above.

 

Cordially,
Chip Pearson
Excel MVP 1998 - 2014
Pearson Software Consulting, LLC
www.cpearson.com

73 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Put this in a module in your workbook and run it and it should give you a new worksheet containing all of the names

 

Option Explicit

Sub ListWorksheets()
Dim aWB As Excel.Workbook
Dim WS As Excel.Worksheet
Dim myWS As Excel.Worksheet
Dim lRow As Long


Set aWB = ActiveWorkbook
On Error Resume Next
Set myWS = aWB.Worksheets("Worksheet Names")
On Error GoTo 0
If myWS Is Nothing Then

    Set myWS = aWB.Worksheets.Add
    myWS.Move before:=aWB.Worksheets(1)
    myWS.Name = "Worksheet Names"
Else
    myWS.UsedRange.ClearContents
End If

lRow = 0
For Each WS In aWB.Worksheets
    lRow = lRow + 1
    myWS.Cells(lRow, 1) = WS.Name
   
Next WS

End Sub

63 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.