Updating Hyperlinks When Page is Copied

[bdaman]

I have a rather large workbook that has a 'Template Page' where the default page styling, formulas, and tables reside. I have a Macro that clones the page, and then auto updates the formulas to refer [NewPage] instead of [Template] page. All works well, no issues. Then now they want to put in hyperlinks to locations on the page since the page is rather tall, at approx 700 line items. So they Can Have a Link at the top of the page to jump to the top cell in each pre defined 'section'. Then in that Section have a link back to the top of the page. Seems to work well for them to jump around.

The Problem is, when I clone a page, the Hyperlinks dont autoupdate. So on [NewPage1] all of the hyperlinks still point to [Template Page]. I can make quick little vba to loop through and tell me the link address of each hyperlink, and it returns $B$572 and not 'Template'$B$572, so I'm failing how to update the hyperlink to be 'NewPage1'$B$572. If the link is an external location like a web page, external file on external drive, I am able to get it to update to point to a new location. I have even been able to update the cell location of a link so $B$572 can be changed to $B$400, but cannot figure out how to update the sheet it is pointing to in VBA.

Thanks in advance.

 

[John_w]

This macro changes the sheet name in the cell reference (place in this document) for all hyperlinks on the active sheet to the name of the active sheet.

Public Sub Change_Hyperlinks_on_Sheet()

    Dim hlink As Hyperlink
    Dim p As Long
    
    With ActiveSheet
        For Each hlink In .Hyperlinks
            If hlink.Address = "" Then
                p = InStrRev(hlink.SubAddress, "!")
                If p > 0 Then
                    hlink.SubAddress = "'" & .Name & "'" & Mid(hlink.SubAddress, p)
                End If
            End If
        Next
    End With
    
End Sub

 

[bdaman]

This macro changes the sheet name in the cell reference (place in this document) for all hyperlinks on the active sheet to the name of the active sheet.

Great, Ill give it a shot this afternoon when I am back in that file. Thank you very much.