Create list of tabs in Excel

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.

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