XLA Routines: EE_ListSheetNames

Nick's picture
Creates an index sheet with hyperlinks
Sub EE_ListSheetNames(Optional NewShtName As String)
'http://excelexperts.com/xla-routines-eeListSheetNames    for updates on this sub routine
' lists the names of the sheets on a new sheet
Dim sht As Worksheet
    If NewShtName = "" Then
        NewShtName = "Index"
    End If
    Call EE_ReplaceSheet(NewShtName)
    i = 1
    On Error Resume Next
    For Each sht In ActiveWorkbook.Sheets
        Cells(1).Offset(i) = sht.Name
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(1).Offset(i), Address:="", SubAddress:= _
            "'" & Cells(1).Offset(i) & "'!A1", TextToDisplay:=Cells(1).Offset(i).value
        i = i + 1
End Sub