macro to move data from one sheet to another when i click on its name

[tonywatsonhelp]

Hi Everyone,
please help if you can.

I need a macro to do the following

In Sheets"Prices" range V28:v228 i have order names

I have the details of all my orders in sheet "Data"
In sheet "data" Column D I have the order names

So this is what i want to do,

Click on an order name from Sheets"Prices" range V28:v228 trigger a macro that will go to
sheet "data" Column D and filter for that name (there will always be at least one row but can be more)

Take the filtered data and paste values only

copy Range C to H
paste values into sales starting at cell AD33

i'm struggling to get the macro to trigger when i click the name?

thanks

Tony

 

[offthelip]

Try this code: I have presumed that the output goes to a tab called Sales:
I thought the easiest way to trigger it is to use the double click event on the "prices" worksheet:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 If Not (Intersect(Target, Range("V28:V228")) Is Nothing) Then
    Dim temparr(1 To 1, 1 To 6) As Variant
    With Worksheets("Data")
     lastrow = .Cells(Rows.Count, "D").End(xlUp).Row
     datar = .Range(.Cells(1, 1), .Cells(lastrow, 8))
    End With
    With Worksheets("Sales")
     lastsales = .Cells(Rows.Count, "AD").End(xlUp).Row + 1
     If lastsales < 33 Then   ' make sure output start in row 33 at least
       lastsales = 33
     End If
     For i = 1 To lastrow
        If Target.Value = datar(i, 4) Then
           For k = 1 To 6
            temparr(1, k) = datar(i, 2 + k) ' copy data to output buffer
           Next k
           .Range(.Cells(lastsales, 30), .Cells(lastsales, 35)) = temparr ' write line out to worksheet
           lastsales = lastsales + 1
        End If
    Next i
    End With
       
 End If
 
End Sub

 

[tonywatsonhelp]

Thanks offthelip,
this is great, sorry it took me a while to reply I've been off for a few days.
Thanks very much for your help
Tony