List Worksheets
Somebody at work called me over recently for some help with a workbook that has over 175 tabs. After I fixed the code, I thought; wouldn’t it be nice if there was a list of all the tabs so I didn’t have to scroll through all of them.I have added this to my custom toolbar that continues to grow.
Sub GetShtNames()
Dim I As LongPtr
Dim Sht As String
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Index WB"
Sht = ActiveSheet.Name
For I = 1 To Sheets.Count
Sheets(Sht).Cells(I, 1) = Sheets(I).Name
Next I
MsgBox "Finished listing " & I-1 & " worksheets to index", vbInformation
End Sub
I am sure there is a more elegant way to get the name of the just created sheet but I was tired and lazy.
|