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