hyperlinks on new worksheets

[coltheplumb]

Hi All, I have just started a work book and the 1^stsheet has lots of hyperlinks which works fine, I now want to duplicate theformat including hyperlinks and make about 10 copies/worksheets, unfortunately thehyperlinks on the new worksheets still ping back to the original sheet, isthere any quick way I can make the hyperlinks on sheet 2 relate to sheet 2,sheet 3 relate to sheet 3 etc ?thanks for your help with this
X

 

[coltheplumb]

Hi all any ideas on this please ?

 

[ask2tsp]

Could you please give an example of a few hyperlinks on the first page?

 

[coltheplumb]

Hi, yes they are just basic hyperlinks all within that worksheet, so from say a15 link down to a97 and then back again from a98 back to a1 and so on , I have about 100 links in that first worksheet, now what I want to do is copy that sheet to make another 10 sheets, all will have the same formatting and wording etc but they need to be individual sheets and so the hyperlinks need to just work within the new sheet. I was just wondering how to set/change the hyperlinks rather than have to go through each individual link to make it stick to that worksheet.

 

[ask2tsp]

Can you give a few examples of how you write the hyperlink functions? Then I can try to investigate how your problem can be solved.

 

[coltheplumb]

Hi again, well I don't write it I just click in the cell that I want hyperlink in and go to hyperlink box and put in the required info?

 

[My Aswer Is This]

I think we need to know where your hyperlinks links to.

Do they all link to a cell on the active sheet?

So on each sheet they link to where on the sheet?

You should be able to look at the link and give us a example where they link to

 

[coltheplumb]

yes all the hyperlinks in worksheet 1 all link to somewhere else in worksheet 1, I want to copy this worksheet with all formatting, formulas and hyperlinks and make that worksheet 2, my problem is that when I do that all the hyperlinks in worksheet 2 always link back to worksheet 1, and I was trying to find a way of changing the links so they only link to the sheet I am on...so links in worksheet 1 link with worksheet 1, all the links in worksheet 2 link to worksheet 2 etc etc, I need to make about 10 of these worksheets

 

[ask2tsp]

run this macro to solve your problem

Sub HyperlinksToSheet()
  Dim sh As Worksheet
  Dim hl As Hyperlink
  
  For Each sh In ThisWorkbook.Worksheets
    For Each hl In sh.Hyperlinks
      hl.SubAddress = sh.Name & Mid(hl.SubAddress, InStr(hl.SubAddress, "!"))
    Next hl
  Next sh
End Sub

 

[ask2tsp]

Could you please tell me if the given solution works for you?