How to lookup a value and copy all matching criteria

[Swissshield]

Dear Sirs,

I have an issue with a VBA task. I've been googling and searching on chandoo and stackoverflow for at least 10h how to solve this task, but the results didn't work out.

I need to double click on a customerID (in Order-sheet) and then all related orders need to be shown line by line on the Invoice-sheet. Is has to be with VBA. The rest of the info (prodID, price,etc.) I can vlookup by myself (through vba) once I have all the orders on the sheet.

Could you please help me with this?

Many thanks in advance!

 

[Chihiro]

Something like this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Invoice")

If ActiveSheet.FilterMode = True Then ActiveSheet.FilterMode = False
If Target.Value = "" Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.ScreenUpdating = False
    Cells.CurrentRegion.AutoFilter Field:=2, Criteria1:=Target.Value, Operator:=xlFilterValues
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:A" & lRow).SpecialCells(xlCellTypeVisible).Copy ws.Cells(17, 2)
    Range("C2:C" & lRow).SpecialCells(xlCellTypeVisible).Copy ws.Cells(17, 3)
    Range("E2:F" & lRow).SpecialCells(xlCellTypeVisible).Copy ws.Cells(17, 4)
    ActiveSheet.Cells.AutoFilter
    Application.ScreenUpdating = True
End If
MsgBox "Data transferred to Invoice sheet"
End Sub

 

[Swissshield]

Hi Chihiro,

Thank you so much! it worked!

Kind Regards,
Swissshield