Automate Hyperlinks

[Mashtonsmith]

Hello..

I need some nifty code that will add hyperlinks to cells where there is a corresponding tab with the same name in the same workbook.

I have a summary worksheet with many test names in column A. There is a tab for each test. I'd like a hyperlink to each tab to save me scrolling across the workbook every time.

Thank you

 

[My Aswer Is This]

Try this:
This script assumes your summary sheet is named Summary

The script will clear all data in column A of sheet named Summary

And then enter all your sheet names into column A of sheet named Summary
And a link to all those sheet.

And each sheet will have a link in Range("A1") which will take you back to the Summary sheet.

Sub AddHyperLinks()
'Modified  11/6/2018  12:04:07 PM  EST
Dim C As Range
Dim i As Long
Dim ans As String
ans = "Summary" 'Modify this name if needed all sheet names will be put in Column "A" of this sheet
Sheets(ans).Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Clear
    For i = 2 To Sheets.Count
    Sheets(ans).Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(ans).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets(ans)
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub