Worksheets.range variable vba

[Tayob]

Hi, I am trying to copy certain row values to another worksheet based on which row the user clicked in Column A. For example if user clicks Cell A3, i want to be able to copy the contents of cells D3, E3, G3 to another worksheet. This code works if i know the user will click A136 but i need this to be a variable range so that whichever cell in Column A is pressed, the copy will happen

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("A136")) Is Nothing Then

Worksheets("Sheet1").Range("D136:D136").Copy _
Destination:=Worksheets("Sheet2").Range("B2")
Worksheets("Sheet1").Range("E136:E136").Copy _
Destination:=Worksheets("Sheet2").Range("B3")
Worksheets("Sheet1").Range("G136:G136").Copy _
Destination:=Worksheets("Sheet2").Range("B4")

End If
End If
End Sub

Any ideas or suggestions on how to get pass this.

 

[bebo021999]

Its not too difficult.
But, which cell in sheet2 to be pasted, if click on next cell in column A sheet1 , i.e, A137?
B5,B6,B7 (downward) or C2,C3,C4(accross)?

 

[bebo021999]

Try this in sheet1 code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 And Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    With Worksheets("Sheet2")
        .Range("B2").Value = Target.Offset(0, 3).Value
        .Range("B3").Value = Target.Offset(0, 4).Value
        .Range("B4").Value = Target.Offset(0, 6).Value
    End With
End Sub

 

[Tayob]

Try this in sheet1 code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count > 1 And Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    With Worksheets("Sheet2")
        .Range("B2").Value = Target.Offset(0, 3).Value
        .Range("B3").Value = Target.Offset(0, 4).Value
        .Range("B4").Value = Target.Offset(0, 6).Value
    End With
End Sub

Thank You for the speedy reply and solution. Your code is working 100%.