Edit hyperlinks

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
On 2002-03-19 11:00, Brian from Maui wrote:
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

Are your hyperlinks to other pages within the workbook? As far as I know, any formulas that have sheet tab names in them will automatically update when you change a sheet tab name.

If your hyperlink is to another named range in your workbook, then changing the sheet name again will automatically update the reference to the named range.

Regards, Duane
 
Upvote 0
Hi Mate

Ermm you changed only the sheet name or WrkBk name?? Should auto amend, or haver i lost the plot/// Idea

Chjhange the name back, if can reememebr te file name file open lists the docd or windows recent or start docs will give them away so full name ther.

No back up eh! now now, you read all my posts i know and how often do i scream BACK UP BACK UP BACK UP! so you could play and carry on on original without agro until FULLY solved.

Also set up another sheet TEST DUMY links oe somethings and play till you get it right, i never work live (thats on the critical current sheet UNLESS its fullt tested)

Also ... no its should be all oK...
 
Upvote 0
Well Brian, I feel your pain, my links don't seem to update when I change a sheet name. But here's some vba to generate links, not sure if it helps.

Sub Workshts()
Dim ws As Worksheet, wsTOC As Worksheet
Dim r As Integer, c As Integer
Application.ScreenUpdating = False
Set wsTOC = ActiveSheet
r = ActiveCell.Row
c = ActiveCell.column
For Each ws In ActiveWorkbook.Worksheets
wsTOC.Hyperlinks.Add _
Anchor:=wsTOC.Cells(r, c), _
Address:="", _
SubAddress:=ws.Name & "!A1", _
TextToDisplay:=ws.Name
r = r + 1
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
As a bit of an add-on to the previous question, I was wondering whether someone could help me adjust the code provided by NateO so that it creates the hyperlinks based on names created in another sheet.

Assmume sheet2 has defined names ranging from Answer1 to Answer100, I want on sheet1 to automatically hyperlink to these names. The cells in Sheet1 will have Answer1 to Answer100 typed in them. Just a bit tedious manually placing them all in.

I tried playing around with it a little, but my low level of VB knowledge makes it a long road.

Any help would be greatly appreciated. Thankyou.
 
Upvote 0
Not sure whether replace can be used here.

Say I have the following on sheet1:


A
Answer1
Answer2
Answer3
Answer4
.......
Answer100

Each of these contains a hyperlink which refers to the same name in sheet2.

Not sure how I would do this using replace, but you maybe on to something. Thanks for that anyway mate.
 
Upvote 0
I've tried to use replace but that doesn't seem to work, or am I missing something again. Like Duane said I can always change the sheet names back to what the hyperlinks had but I was just curious if it could be done without doing each hyperlink one by one or by changing the sheet name to the original
 
Upvote 0
This macro will change the destinationaddress from the old sheetnames to the new ones.
Just write the old sheetnames in column E and beside them in column F the new name of the sheet.
Adjust the range in the macro according to your needs.

Sub ChangeHyperlinks()
Dim newones As Variant
newones = Sheets(1).Range("e1:f3")
Dim rSH As Worksheet
For Each rSH In ActiveWorkbook.Worksheets
For Each h In Worksheets(rSH.Name).Hyperlinks
g = h.SubAddress
For x = 1 To UBound(newones)
If InStr(1, g, newones(x, 1)) Then h.SubAddress = Replace(g, newones(x, 1), newones(x, 2))
Next
Next
Next
End Sub


regards Tommy
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top