CODEWITHSUNDEEP
vbaexcel
Dennis Roberts
Dennis Roberts

Reputation: 1

Create a hyperlink list of sheets in excel

I know how to populate a sheet with specific hyperlinks to all the sheets in a given workbook, but how do I exclude certain sheets from being listed?

I have listed the VB below for what I was using. I would like to exclude certain sheets like "Test Results" etc.

Sub GetHyperlinks()
    Dim ws As Worksheet
    Dim i As Integer

    i = 9

    For Each ws In ThisWorkbook.Worksheets
        ActiveWorkbook.Sheets("overview").Hyperlinks.Add _
        Anchor:=ActiveWorkbook.Sheets("overview").Cells(i, 1), _
        Address:="", _
        SubAddress:="'" & ws.NAme & "'!A1", _
        TextToDisplay:=ws.NAme

        i = i + 1
    Next ws
End Sub

Upvotes: 0

Views: 418

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

Sub GetHyperlinks()
    Dim arrExclude
    Dim ws As Worksheet
    Dim i As Integer

    'Sheets to be excluded from linking
    arrExclude = Array("Test Results", "some other sheet", "overview")

    i = 9

    For Each ws In ThisWorkbook.Worksheets
        'test to see if not excluded
        If IsError(Application.Match(ws.Name, arrExclude, 0)) Then
            ActiveWorkbook.Sheets("overview").Hyperlinks.Add _
                Anchor:=ActiveWorkbook.Sheets("overview").Cells(i, 1), _
                Address:="", _
                SubAddress:="'" & ws.Name & "'!A1", _
                TextToDisplay:=ws.Name
            i = i + 1
        End If 'include this sheet
    Next ws
End Sub

Upvotes: 1

Related Questions