Copy cells from one sheet to another if condition met

[Hyakkivn]

Hello brothers/sisters.
I would like to ask for any solution for a task please.
I have 2 sheets in 1 workbook as below... The first one is Export, and second one is Bill.
I want that when I change the Bill No in sheet("Bill"), excel will list all product at Name of Product column in sheet("Export") with same Bill No to sheet Bill. Paste location start from cell C9

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Bill" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a Bill No in cell G2 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "G2" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets("Export")
        .Range("A3", .Range("A" & .Rows.Count).End(xlUp)).AutoFilter 1, Target
        .Range("D4", .Range("D" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy Cells(Rows.Count, "C").End(xlUp).Offset(1)
        .Range("A3").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

 

[Hyakkivn]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Bill" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a Bill No in cell G2 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "G2" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets("Export")
        .Range("A3", .Range("A" & .Rows.Count).End(xlUp)).AutoFilter 1, Target
error on this line >>>        .Range("D4", .Range("D" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy Cells(Rows.Count, "C").End(xlUp).Offset(1)
        .Range("A3").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Thanks for your code. But it has some problem with copy. VBA notice that Copy method of range class failed.
Btw, which key is RETURN key???

 

[mumps]

I tested the macro using some dummy data and it worked properly. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. The RETURN key is the ENTER key.