[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel 2010 edit Hyperlink when sheet copied.

Posted on 2012-09-19
2
Medium Priority
?
441 Views
Last Modified: 2012-09-20
Pre-amble:-
I have a sheet that has a macro to create a copy of it.
The sheet has hyperlinks that link to various cells within the same sheet.

Problem:-
When the sheet is copied the hyperlinks in "CopySheet" still link back to the cells in "OrigSheet".
From what i can tell this is stored in SubAddress but i cant see how i can change all hyperlinks on the "CopySheet".

Any assistance greatly appreciated.

Cheers
Andrew
0
Comment
Question by:Andrew Davis
2 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 38418124
Hi Andrew, try this


Sub UpdateHyperlinks()
Dim hl As Hyperlink

For Each hl In ActiveSheet.Hyperlinks
    hl.SubAddress = Replace(hl.SubAddress, "OrigSheet", "CopySheet")
Next

End Sub

Open in new window

0
 
LVL 19

Author Closing Comment

by:Andrew Davis
ID: 38420243
So simple and so close to what i had.... Doh!

Thanks.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question