Creating worksheet tab names in a distinct and uneatable list is a tedious task. Using Excel VBA is the answer to this problem. Over the years I have worked with some large workbooks. At times finding sheets can be difficult. Also keeping track of all the sheets in the workbook via an index is often useful. In this case it could be advantageous to create a list of worksheets that can be updated by clicking a button or by merely clicking on the Excel sheet in question.
The following is the procedure to create the sheet tabs in a new worksheet entitled AllSheets. it is attached to a regular module. There is a file attached at the bottom of the page to help if you need a working copy of the code.
Option Explicit
Sub ListSheets() 'Excel VBA to list sheet names.
Dim i As Integer
Dim sh As Worksheet
Const txt = "AllSheets"
If Not Evaluate("ISREF('" & txt & "'!A1)") Then 'Check for AllSheets tab.
Set sh = Worksheets.Add
sh.Name = txt
sh.[A1] = "Workbook Sheets"
End If
Set sh = Sheets(txt)
For i = 1 To Worksheets.Count
sh.Cells(i + 1, 1) = Sheets(i).Name
Next i
End Sub
The following file should help to crystallise the concept. Enjoy.
