populate cell via a cursor 'selected' cell in a different sheet

[tbablue]

Hi

Can anyone help me with a piece of code that will populate a cell on one sheet (Sheet1!$A$1) with the value in a cursor 'selected' cell from a specified range within another sheet (Sheet2!$A$1:$A$100) when I hit my return button?

Any assistance welcomed.

 

[Smitty]

How's this:

<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("A1:A100")<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</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            Sheets("Sheet1").Range("A1").Value = Target.Value<br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[VoG]

Try this: right click Sheet2's tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
Sheets("Sheet1").Range("A1").Value = Target.Value
End Sub

 

[mikerickson]

You could use this UDF. Put =LastSelected() in Sheet1A1.

Public LastSelectedValue As Variant

Function LastSelected()
    Application.Volatile
    If Not Application.Intersect(Selection, ThisWorkbook.Sheets("Sheet2").Range("A1:A100")) Is Nothing Then
        LastSelectedValue = ActiveCell.Value
    End If
   LastSelected = LastSelectedValue
End Function

 

[tbablue]

Appreciated guys.

My cup runneth over. Thanks again.

 

[tbablue]

Try this: right click Sheet2's tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
Sheets("Sheet1").Range("A1").Value = Target.Value
End Sub

Hi guys.

I guess it's a simple ask but how would I hyperlink to Sheets("Sheet1").Range("A1") when it's selected?