Use this bit of VBA to create a list of Excel sheet name. – EASY
Just add this code in VBA to the ThisWorkbook node and press F5
Sub ListSheetNamesInNewWorkbook()
Dim objNewWorkbook As Workbook
Dim objNewWorksheet As Worksheet
Set objNewWorkbook = Excel.Application.Workbooks.Add
Set objNewWorksheet = objNewWorkbook.Sheets(1)
For i = 1 To ThisWorkbook.Sheets.Count
objNewWorksheet.Cells(i, 1) = i
objNewWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i
With objNewWorksheet
.Rows(1).Insert
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Font.Bold = True
.Cells(1, 2) = "NAME"
.Cells(1, 2).Font.Bold = True
.Columns("A:B").AutoFit
End With
End Sub
All credit goes to https://www.datanumen.com/blogs/3-quick-ways-to-get-a-list-of-all-worksheet-names-in-an-excel-workbook/