Linking cells on one sheet with same cell on another sheet.

[amattey]

Hello,

I have been looking for a way to "hyperlink" each cell in one sheet with its correspondent cell in another sheet. For example if I am in sheet 1 cell A1, I want to click in it and it will take me to cell A2 on sheet 2. I can do each one by hand but would like to do all cells witin the column. Can anyone help me here? Is it possible?

 

[Smitty]

Welcome to the Board!

Do you mean A1 to A1 (not A1 to A2)? If so, then this should do what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br>    Cancel = <SPAN style="color:#00007F">True</SPAN><br>    Application.Goto Reference:=Sheets("Sheet2").Range(Target.Address)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[amattey]

And could I just drag in the formula all the way down?

 

[Smitty]

You don't need to do anything at all. Just double-click on any cell in sheet one and you'll be taken to the same cell in sheet 2.

 

[amattey]

I put that code in my VBA but it seems to not work. Maybe I am doing it incorrectly. Could you advise?

 

[Smitty]

The code needs to go in the Worksheet specific module, not a general module. The quickest way to get there is to right-click the sheet tab and select View Code.

 

[amattey]

Ok, I did and it works. Is there any way to block this code so it only does the cells in a specific column?

 

[Smitty]

Sure, here's one way (this limits the code to column C) :

If Target.Column <> 3 Then Exit Sub

You can also define ranges like you'll see in this example:
<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>