Finding and replacing formulae with Hyperlinks (referencing another worksheet within the workbook)

CeeB1

New Member
Joined
Sep 10, 2015
Messages
12
I need to change a value within the formulae to reflect another worksheet. These hyperlinks are jumping to different worksheets within the SAME workbook and I need to be able to change the worksheet name to which it need to jump to (for multiple hyperlinks). The find and replace function for that specific test within the Hyperlink formulae does not work when it refers to another worksheet within the workbook - I have seen solutions to change an external web address which is not what I need.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi CeeB1,

You could make the sheet name a variable, or maybe use CELL to get the target which can then be changed with a find and replace.

CeeB1.xlsx
DEF
1Sheet 2#'Sheet 2'!$E$9
2
3#'[CeeB1.xlsx]Sheet 3'!$E$9
Sheet1
Cell Formulas
RangeFormula
F1F1=HYPERLINK("#'"&D1&"'!$E$9")
F3F3=HYPERLINK("#"&CELL("address",'Sheet 3'!$E$9))
 
Upvote 0
Hi CeeB1,

You could make the sheet name a variable, or maybe use CELL to get the target which can then be changed with a find and replace.

CeeB1.xlsx
DEF
1Sheet 2#'Sheet 2'!$E$9
2
3#'[CeeB1.xlsx]Sheet 3'!$E$9
Sheet1
Cell Formulas
RangeFormula
F1F1=HYPERLINK("#'"&D1&"'!$E$9")
F3F3=HYPERLINK("#"&CELL("address",'Sheet 3'!$E$9))
Thank you very much, that will help with establishing new hyperlinks going forward but it does not help with the thousands which I already have and need to change individually.
 
Upvote 0
Please give some examples showing which need change and which don't.
 
Upvote 0
Please give some examples showing which need change and which don't.
Dear Toadstool
Herewith a snapshot, I need to change the reference from 'ACT 2022' to 'BUD 2022', and 'PLN 2023', 'PLN 2024',.... respectively
Hyperlink Find & Replace Issue.JPG
 
Upvote 0
Ah! I thought you were using the HYPERLINK function.

Such changes should be possible through VBA but that's beyond my knowledge.
 
Upvote 0
Thank you very much for trying - maybe someone else with VBA skills will come to the rescue.
 
Upvote 0
Try this on a copy of your workbook. Note the separate functions being dependencies of the RelinkHyperlinks function.

VBA Code:
Public Sub CeeB1()
    ' usage examples
    '                                 old    ,    new
    RelinkHyperlinks ThisWorkbook, "ACT 2022", "BUD 2022"
   'RelinkHyperlinks ActiveWorkbook, "ACT 2022", "BUD 2022"
End Sub

Public Function RelinkHyperlinks(ByVal argWb As Workbook, ByVal argOldRefName As String, ByVal argNewRefName As String)
    Dim Sht As Worksheet, Hl As Hyperlink
    For Each Sht In argWb.Worksheets
        For Each Hl In Sht.Hyperlinks
            If VBA.InStr(1, Hl.SubAddress, ShtNameFromRef(argOldRefName), vbTextCompare) > 0 Then
                If WorksheetExists(Sht.Parent, ShtNameFromRef(argNewRefName)) Then
                    Hl.SubAddress = VBA.Replace(Hl.SubAddress, BuildRef(argOldRefName), BuildRef(argNewRefName), , , vbTextCompare)
                End If
            End If
        Next Hl
    Next Sht
End Function

Public Function ShtNameFromRef(ByVal argRefName As String) As String
    ShtNameFromRef = VBA.Split(VBA.Replace(argRefName, "'", ""), "!")(0)
End Function

Public Function BuildRef(ByVal argRefName As String) As String
    argRefName = VBA.Replace(argRefName, "'", "")
    BuildRef = VBA.IIf(VBA.InStr(1, argRefName, " ") > 0, "'" & VBA.Replace(argRefName, "!", "'!"), argRefName)
End Function

Function WorksheetExists(ByVal argWb As Workbook, ByVal argShtName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Not argWb.Worksheets(argShtName) Is Nothing
End Function
 
Upvote 0
Try this on a copy of your workbook. Note the separate functions being dependencies of the RelinkHyperlinks function.

VBA Code:
Public Sub CeeB1()
    ' usage examples
    '                                 old    ,    new
    RelinkHyperlinks ThisWorkbook, "ACT 2022", "BUD 2022"
   'RelinkHyperlinks ActiveWorkbook, "ACT 2022", "BUD 2022"
End Sub

Public Function RelinkHyperlinks(ByVal argWb As Workbook, ByVal argOldRefName As String, ByVal argNewRefName As String)
    Dim Sht As Worksheet, Hl As Hyperlink
    For Each Sht In argWb.Worksheets
        For Each Hl In Sht.Hyperlinks
            If VBA.InStr(1, Hl.SubAddress, ShtNameFromRef(argOldRefName), vbTextCompare) > 0 Then
                If WorksheetExists(Sht.Parent, ShtNameFromRef(argNewRefName)) Then
                    Hl.SubAddress = VBA.Replace(Hl.SubAddress, BuildRef(argOldRefName), BuildRef(argNewRefName), , , vbTextCompare)
                End If
            End If
        Next Hl
    Next Sht
End Function

Public Function ShtNameFromRef(ByVal argRefName As String) As String
    ShtNameFromRef = VBA.Split(VBA.Replace(argRefName, "'", ""), "!")(0)
End Function

Public Function BuildRef(ByVal argRefName As String) As String
    argRefName = VBA.Replace(argRefName, "'", "")
    BuildRef = VBA.IIf(VBA.InStr(1, argRefName, " ") > 0, "'" & VBA.Replace(argRefName, "!", "'!"), argRefName)
End Function

Function WorksheetExists(ByVal argWb As Workbook, ByVal argShtName As String) As Boolean
    On Error Resume Next
    WorksheetExists = Not argWb.Worksheets(argShtName) Is Nothing
End Function
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top