Macro repairing links in hyperlink

T

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.
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top