Excel Macro - Using For Each Loop To Hyperlink Back to Main / Index Pages

Question

I have a workbook with many sheets (365++) . Meanwhile there are many examples VBA code that allows macro creating hyperlinks from index pages to the contents ; however there are totally zero examples of creating hyperlinks from contents back to index pages.

Using the loop thru solutions, I can get the code to work BUT it only works for existing active pages whereas I need it to loop every single sheet range "A1" in the workbook to link it back to main pages

Here are code as follows:

Sub vba_loop_sheets()

Dim ws As Worksheet

    For Each ws In Worksheets
        'Dummy data to test for loop
        'ws.Range("A1").Value = "Dummy"
        
        'Only works for existing sheets,need to loop for each loops of each sheets
        Range("A1").Select
        ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="", SubAddress:="Main!A1", TextToDisplay:="Back to Main Sheet"
    Next ws

End Sub

teylyn teylyn · Accepted Answer · 2020-08-30T22:15:10

You start a loop through all worksheets, but inside that loop you reference only the active sheet, without ever changing the active sheet.

You don't need ActiveSheet or Select. Instead, use the ws sheet from the loop to qualify the sheet for the .Hyperlinks.Add and for the anchor range. Like this:

Sub vba_loop_sheets()

Dim ws As Worksheet

    For Each ws In Worksheets
        ws.Hyperlinks.Add Anchor:=ws.Range("A1"), Address:="", SubAddress:="Main!A1", TextToDisplay:="Back to Main Sheet"
    Next ws

End Sub

Excel Macro - Using For Each Loop To Hyperlink Back to Main / Index Pages

1 Answers