Macro repairing links in hyperlink

[ThomasX]

hello,

I have a excel file hyperlinks to other pages in file. I changed the name of sheets and hyperlinks do not work. I want to write a macro that will fix the hyperlink.

Can you help me.

 

[Claus Busch]

Hi Thomas,

Am Mon, 4 Feb 2013 03:41:19 -0800 (PST) schrieb ThomasX:

I have a excel file hyperlinks to other pages in file. I changed the name of sheets and hyperlinks do not work. I want to write a macro that will fix the hyperlink.

modify to suit:

Sub ChangeHyperlink()
Dim i As Long
Dim HypOld As String
Dim HypNew As String

'Modify old and new sheet name
HypOld = "Sheet3"
HypNew = "Data"

With ActiveSheet
For i = 1 To .Hyperlinks.Count
With .Hyperlinks(i)
.SubAddress = Replace(ActiveSheet.Hyperlinks(i).SubAddress, _
HypOld, HypNew)
.TextToDisplay = "Goto " & HypNew
End With
Next
End With
End Sub


Regards
Claus Busch