Find & Replace Hyperlinks

T

trickytrev

Does anyone know of a way in which you can find and replace hyperlink
of one address with an alternate one that can be entered ?

I am using Excel 2000 and want to write a macro or something that wil
allow me rename a large number of sheets and rename all of th
hyperlinks on each of those sheets.

Any help you can give would be greatly appreciated.

Cheers

Tre
 
B

Bernie Deitrick

Trev,

Sub ChangeAllHyperLinks()
Dim mySht As Worksheet
Dim myHyper As Hyperlink
Dim oldStr As String
Dim newStr As String

oldStr = InputBox("Replace Address?")
newStr = InputBox("New Address?")

For Each mySht In Worksheets
For Each myHyper In mySht.Hyperlinks
myHyper.Address = _
Replace(LCase(myHyper.Address), oldStr, newStr)
myHyper.TextToDisplay = Replace( _
LCase(myHyper.TextToDisplay), oldStr, newStr)
Next myHyper
Next mySht
End Sub

HTH,
Bernie
MS Excel MVP

trickytrev said:
Does anyone know of a way in which you can find and replace hyperlinks
of one address with an alternate one that can be entered ?

I am using Excel 2000 and want to write a macro or something that will
allow me rename a large number of sheets and rename all of the
hyperlinks on each of those sheets.

Any help you can give would be greatly appreciated.

Cheers

Trev
Click to expand...
creating financial statements
 
Top