Help me modify this Double Click VBA

[JumboCactuar]

Hi,

i have this VBA code which if a cell in the range E8:AF10 is clicked, the cell value is copied to Sheet 2 - B3

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)If Not Intersect(Target, Range("E8:AF10")) Is Nothing Then
    Cancel = True
    Target.Copy
    Sheets("Sheet2").Range("B3").PasteSpecial Paste:=xlPasteValues
End If
End Sub

Now instead of copying the clicked cell i want it to copy 2 things:
Value in Row 6 of the current cell
example: If cell F9 is clicked > copy cell F6

Value in Column AI of the current cell
example: If F9 is clicked > copy cell AI9

Appreciate any help

 

[Fluff]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("E8:AF10")) Is Nothing Then
    Cancel = True
    Cells(6, Target.Column).Copy
    Sheets("Sheet2").Range("B3").PasteSpecial Paste:=xlPasteValues
    Range("AI" & Target.Row).Copy
    '???
End If
End Sub

 

[JumboCactuar]

How about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("E8:AF10")) Is Nothing Then
    Cancel = True
    Cells(6, Target.Column).Copy
    Sheets("Sheet2").Range("B3").PasteSpecial Paste:=xlPasteValues
    Range("AI" & Target.Row).Copy
    '???
End If
End Sub

thanks for the quick reply, working perfectly

 

[Fluff]

Glad to help & thanks for the feedback