How to automatically change hyperlink destination in Excel

Question

Is there a function in Excel that will automatically change the hyperlink (hyperlink from a cell on one sheet to cells on another sheet) destination?

For example, if my hyperlink was to a set of cells on a different worksheet, and I moved those cells around on the different worksheet, is there a way for the hyperlink destination to change accordingly to where those cells have been moved?

Thanks!

Answer 1

Sub ReplaceHyperlinks()
Dim Ws As Worksheet
Dim xHyperlink As Hyperlink
Dim xOld As String, xNew As String
xTitleId = "ReplaceHyper"
Set Ws = Application.ActiveSheet
xOld = Application.InputBox("Old text:", xTitleId, "", Type:=2)
xNew = Application.InputBox("New text:", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For Each xHyperlink In Ws.Hyperlinks
    xHyperlink.Address = Replace(xHyperlink.Address, xOld, xNew)
Next
Application.ScreenUpdating = True
End Sub