Make Excel hyperlinks local to current sheet

[patme]

I have a sheet, SHEET1, that I have some hyperlinks that jump from cell b10 to cell ac55. Once I have SHEET1 formatted the way I want, I want to copy it to SHEET2. The problem is, the hyperlinks are copied, but in SHEET2 they still point back to 'Sheet1'!AC55.

Am I creating them wrong? I simply right-click, select Hyperlink, and type in the cell reference.

Thanks.

 

[StuckInTheMiddle]

A workaround for you is that you could do a find/replace on your 2nd sheet and look for 'Sheet1'! and replace it with Sheet2'! for all occurences in that sheet for all your formulas.

A,

 

[patme]

No, already thought of that. OR, maybe but I'm not doing it correctly! I don't think you can.

 

[StuckInTheMiddle]

Yes, you can do it, I've done it many times for users who want to do the same thing you are doing. What are you searching/replacing, tell me exactly.

 

[patme]

Okay.
When I enter a new hyperlink, it assumes that I want the current sheet, which I do. When I hover my mouse over the link, the following appears:
file:///c:/mypath/file.xls - 'sheet1'!ac66
Then, when I copy sheet1 to sheet2, and hover over the links in sheet2, they still read as shown above.

I want them to follow the CURRENT sheet name...that's all!

Thanks in advance for any help.

 

[bkpchs237]

patme,
You will need to change the subaddress property of the hyperlinks on this worksheet. You can use something similar to the following code, which changes items that have hyperlinks in cells D3 thru D12 to places on Sheet1 on the current sheet to those same cells on the current worksheet.

Option Explicit

Sub ChangeSubAddress()
Dim i As Integer
For i = 3 To 12
If Left(Cells(i, 4).Hyperlinks.Item(1).SubAddress, 6) = "Sheet1" Then
Cells(i, 4).Hyperlinks.Item(1).SubAddress = ActiveSheet.Name & _
Right(Cells(i, 4).Hyperlinks.Item(1).SubAddress, Len(Cells(i, 4).Hyperlinks.Item(1).SubAddress) - 6)
End If
Next i
End Sub

Hope this helps.

 

[byundt]

The HYPERLINK function, working together with CELL and FIND functions, can give you a hyperlink that will always jump to the same cell--even after you copy a worksheet.
=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),FIND("]",CELL("filename"))-FIND("[",CELL("filename"))+1)&"'"&MID(CELL("filename"),FIND("]",CELL("filename"))+1,99)&"'!AC55","AC55")
Brad