Excel VBA

css navigation by Css3Menu.com

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.


Updated:  11/16/2016 19:50
This page added:  16 November 2016