show selected or clicked cell value from one column in other cell

[ict_shiraz]

Hi Dear,
i have a simple sheet with some columns, A to D and column B contains names like john, david,simon and etc.
i used below command to show selected or clicked cell value in other cell (M2).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("M2").Value = Target.Value
End Sub


But when i want to change that command for specific range of column B (B3 to B2000) by using commands in below it doesn't work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("M2").Value = Range("B2:B2000").Value
End Sub
Or
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("M2").Value = Range("B2:B2000").Select
End Sub

I wanna do it for this range only and take no effect on M2 cell value when i select out ranged value and just change other parts manually and commonly.

Kindly advise.

 

[Herakles]

Hi Dear,
i have a simple sheet with some columns, A to D and column B contains names like john, david,simon and etc.
i used below command to show selected or clicked cell value in other cell (M2).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("M2").Value = Target.Value
End Sub


But when i want to change that command for specific range of column B (B3 to B2000) by using commands in below it doesn't work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("M2").Value = Range("B2:B2000").Value
End Sub
Or
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("M2").Value = Range("B2:B2000").Select
End Sub

I wanna do it for this range only and take no effect on M2 cell value when i select out ranged value and just change other parts manually and commonly.

Kindly advise.

So you want the values in 1999 cells to be put into one cell?

Can you give us an idea of what this cell will then look like?

 

[mumps]

Does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("B3:B2000")) Is Nothing Then Exit Sub
    Range("M2").Value = Target.Value
End Sub

 

[ict_shiraz]

Does this do what you want?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("B3:B2000")) Is Nothing Then Exit Sub
    Range("M2").Value = Target.Value
End Sub

Dear Mumps,
I do appreciate . exactly your VBA code is what I want.
Best wishes;

 

[mumps]

My pleasure.