VBA to VLookup order# in master data, copy and paste rows in another sheet, then delete rows from master

[TheGrandPooba]

Hello all, this is my first post, and feedback would be appreciated.

I would like to create a button below a standalone "Completed Order" cell to vlookup the order# in the master data sheet. The master table is named 'OImport,' and the order# header is [Order'#]. For any table rows that have the Completed Order#, I would like to copy and paste them in an 'OrderHistory' table on another sheet, then delete those rows from 'OImport.'

A couple points to note: The "Completed Order" cell would be manually typed in 'Summary' sheet G3, then the button would be clicked. Also, the order# could be in 1-5 rows in the master data sheet depending on the number of item's ordered. Lastly, after the rows are copy/pasted and deleted, could the "Completed Order" cell be cleared? ('Summary' G3)

Appreciate any help, I'm fairly new to the VBA language and couldn't find this particular request online.
Thanks!

 

[mumps]

Please post updated data.

 

[TheGrandPooba]

As you can see, it left only the orders with 266835, but didn't delete them. The OrderImport can be up to 500 rows long, so I couldn't copy the table using XL2BB. As you can see in the second photo, there is other data to the right of the table that I'm guessing is preventing the OImport rows from deleting.

I can just unhide the rows in OrderImport to undo the break in the macro, but if there was a way for only the table rows to be deleted, I believe your macro would execute properly (on my end.)
Thanks for troubleshooting with a total beginner, you are a life saver

 

[mumps]

This appears to work for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "G3" Then Exit Sub
    Dim srcWS As Worksheet, desWS As Worksheet, rng As Range
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
    With srcWS.ListObjects("OImport")
        .Range.AutoFilter 1, Target.Value
        .DataBodyRange.SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        Set rng = .DataBodyRange.SpecialCells(xlCellTypeVisible).Cells
        .DataBodyRange.AutoFilter Field:=1
        rng.Delete
    End With
    Application.ScreenUpdating = True
End Sub

 

[TheGrandPooba]

This appears to work for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "G3" Then Exit Sub
    Dim srcWS As Worksheet, desWS As Worksheet, rng As Range
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
    With srcWS.ListObjects("OImport")
        .Range.AutoFilter 1, Target.Value
        .DataBodyRange.SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        Set rng = .DataBodyRange.SpecialCells(xlCellTypeVisible).Cells
        .DataBodyRange.AutoFilter Field:=1
        rng.Delete
    End With
    Application.ScreenUpdating = True
End Sub

Thank you so much mumps!!! It fully cleared off of OrderImport and into OrderHistory :D
For the last time, thanks again for the persistence and being willing to accomodate my large request.
Thank you!!

 

[mumps]

My pleasure.