Copying Excel sheets with Hyperliknks. Without Sheets reference-CodePudding

I have hyperlinks in an big excel sheet that point to certain cells. (e.g. Link to cell A1, to AR50 etc..just to jump to different positions in the sheet) When duplicate/copying these sheets, the reference (e. A1) is changed to "sheetname!A1". So i have to change all references in the duplicated sheet. has anyone an idea how to copy it just without the sheets reference?? Thank you

CodePudding user response：

I don't think you can do this when you're inserting hyperlinks by using the "Insert Hyperlink dialog box". However, it can be accomplished by using the HYPERLINK formula. E.g. enter =HYPERLINK("#A100","Link") in the cell to establish a link. You'll see a clickable text "Link" (you can change this text to whatever you like, of course). Clicking the cell will route you to A100 in your current sheet. Once copied, the same cell in the copy will route you to A100 in this new sheet.

CodePudding user response：

If you have used the Insert -> Link feature to create the links then you will need to use a VBA routine, such as the following

Sub updateLinks()
    Const srcSheet As String = "SheetOne"
    Const copySheet As String = "Sheet Two"
    Dim link As Hyperlink
    For Each link In Worksheets(copySheet).Hyperlinks
        If InStr(1, link.SubAddress, srcSheet) Then
            link.SubAddress = "'" & copySheet & "'!" & Split(link.SubAddress, "!")(1)
        End If
    Next link
End Sub

this example assumes that the tab copied had the name SheetOne, and that the copy was named Sheet Two (if you don't know where to put this code then please see this article)