Edit hyperlinks

[Brian from Maui]

Aloha,

I renamed my worksheets in a workbook not realizing that it breaks the hyperlinks. I tried using "replace" but that does not seem to work. I can't rename the the sheets, as I have other formulas with the new worksheet names. Anyone have any ideas, besides doing it one by one?

Brian

 

[Tommy Bak]

Reggie
i'm not quite sure what you mean, but this macro will search the workbook for names and will make a hyperlink to each name in 1. worksheet column A

Sub MakelinksToName()
For Each n In ActiveWorkbook.Names
x = x + 1
Sheets(1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", SubAddress:= _
n.Name
Next
End Sub

regards Tommy

 

[Tommy Bak]

Reggie
This macro will limit the names to those on sheet 2

Sub MakelinksToName()
For Each n In ActiveWorkbook.Names
If InStr(1, n.RefersTo, Sheets(2).Name) Then
x = x + 1
Sheets(1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", SubAddress:= _
n.Name
End If
Next
End Sub

 

[Reggie]

Thankyou Tommy. Works well.

I was wondering if this coding can be modified so that it limits the creation of hyperlinks to names located in a particular column in sheet 2? My sheet has many hundreds of names and limiting to a particular row would be helpful.

Thankyou again very much for your help.

 

[Tommy Bak]

Hi Reggie
Modified code. Run this while being on sheet 1. This will ask you for a column. Just point to sheet 2 and a select a cell in the column you want to list hyperlinks for.


Sub MakelinksToName()
Dim c As Range
Set c = Application.InputBox("Which column ?", , , , , , ,
col = c.Column
For Each n In ActiveWorkbook.Names
If InStr(1, n.RefersTo, Sheets(2).Name) Then
Set t = Range(Mid(n.RefersTo, Len(Sheets(2).Name) + 3))
If t.Column = col Then
x = x + 1
Sheets(1).Hyperlinks.Add Anchor:=Cells(x, 1), Address:="", SubAddress:= _
n.Name
End If
End If
Next
End Sub

regards Tommy

 

[Tommy Bak]

In a line I got a smiley. Change to:
Set c = Application.InputBox("Which column ?", , , , , , , 8 )

 

[Reggie]

Thankyou very much Tommy.

Your help has saved me a lot of time and frustration.