Hyperlink with a relitive reference.

%3CLINGO-SUB%20id%3D%22lingo-sub-1510257%22%20slang%3D%22en-US%22%3EHyperlink%20with%20a%20relitive%20reference.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510257%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20worksheet%20('mysheet')%26nbsp%3B%20that%20contains%20a%20link%20to%20a%20cell%20in%20the%20same%20sheet.%20I%20need%20to%20make%20a%20copy%20('mysheet%20(1)'%20)%26nbsp%3B%20When%20I%20do%2C%20the%20link%20takes%20me%20back%20to%20the%20reference%20in%20the%20original%20worksheet%20('mysheet').%20I%20want%20it%20to%20adjust%20each%20time%20I%20make%20a%20copy%20and%20take%20me%20to%20the%20same%20cell%20in%20the%20sheet%20I%20have%20copied.%20Is%20this%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1510257%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1510524%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20with%20a%20relitive%20reference.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1510524%22%20slang%3D%22en-US%22%3EI'm%20not%20sure%20how%20or%20if%20it%20can%20be%20done%20through%20the%20normal%20excel%20dialog%20as%20it%20adds%20the%20worksheet%20reference%20to%20the%20cell%20address%2C%20but%20it%20appears%20to%20work%20if%20you%20do%20it%20using%20vba.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20copy%2Fpaste%20this%20macro%20into%20a%20module%20and%20run%20it%2C%20then%20it%20would%20add%20a%20link%20to%20the%20active%20cell.%20Change%20the%20cell%20address%20and%20text%20to%20display.%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20Macro2()%3CBR%20%2F%3EActiveSheet.Hyperlinks.Add%20_%3CBR%20%2F%3EAnchor%3A%3DSelection%2C%20_%3CBR%20%2F%3EAddress%3A%3D%22%22%2C%20_%3CBR%20%2F%3ESubAddress%3A%3D%22A2%22%2C%20_%3CBR%20%2F%3ETextToDisplay%3A%3D%22LINK%22%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1519168%22%20slang%3D%22en-US%22%3ERe%3A%20Hyperlink%20with%20a%20relitive%20reference.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1519168%22%20slang%3D%22en-US%22%3EI%20was%20hoping%20to%20do%20it%20without%20macros%2C%20But%20thank%20you%20for%20this%20response.%20I%20will%20try%20it.%3C%2FLINGO-BODY%3E
New Contributor

I have a worksheet ('mysheet')  that contains a link to a cell in the same sheet. I need to make a copy ('mysheet (1)' )  When I do, the link takes me back to the reference in the original worksheet ('mysheet'). I want it to adjust each time I make a copy and take me to the same cell in the sheet I have copied. Is this possible?

2 Replies
I'm not sure how or if it can be done through the normal excel dialog as it adds the worksheet reference to the cell address, but it appears to work if you do it using vba.

If you copy/paste this macro into a module and run it, then it would add a link to the active cell. Change the cell address and text to display.

Sub Macro2()
ActiveSheet.Hyperlinks.Add _
Anchor:=Selection, _
Address:="", _
SubAddress:="A2", _
TextToDisplay:="LINK"
End Sub


I was hoping to do it without macros, But thank you for this response. I will try it.