Get the names of worksheets / tabs

[wwj]

Get the names of worksheets / tabs
Note that Chr(34) is used for double quotation mark.

Code: Select all

Sub get_tab_names()
    
    Dim i  As Integer
    
    ThisWorkbook.Activate
    
    listTab = "LEGEND"
    
    Sheets(listTab).Cells(1, "J") = "Tab Names"
    Sheets(listTab).Cells(1, "K") = "Link"
    rowNo = 2
    
    For i = 1 To Worksheets.Count
        ws = Worksheets(i).Name
        Sheets(listTab).Cells(rowNo, "J") = ws
        
        ' Hyperlink for tabs.
        pageLink = "=HYPERLINK(" & Chr(34) & "#'" & ws & "'!A30" & Chr(34) & "," & Chr(34) & ws & Chr(34) & ")"
        Sheets(listTab).Cells(rowNo, "K") = pageLink
            
        rowNo = rowNo + 1
        
    Next i
    
    MsgBox "Done!"
    
End Sub