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

T

TheGrandPooba

New Member
Joined
Jul 1, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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 three sheets. Alternately, you could upload a copy of your file 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. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Here are the additional XL2BB of the 3 sheets:
Tab named "Summary" - not a table
FargoPlan.xlsm
ABDEFGHIJK
1
2
3Completed order?266931
4
5
6Orders Sorted by ShipDate
7All OrdersItemTypeOrder UIDBillToShipToPO#OrderDateShipDateStatus
82668612604Past266861_2604SPECIALTY COMMODITIES SPECIALTY COMMODITIES 1012025B 5/25/226/3/22NA
92668041065Past266804_1065TRADER JOE'S COMPANY TRADER JOE'S / WCD IRVING 5303 1324191865/17/226/9/22Completed
102668041066OilR/S266804_1066TRADER JOE'S COMPANY TRADER JOE'S / WCD IRVING 5303 1324191865/17/226/9/22Scheduled
112668041067OilRNS266804_1067TRADER JOE'S COMPANY TRADER JOE'S / WCD IRVING 5303 1324191865/17/226/9/22Completed
122668051065Past266805_1065TRADER JOE'S COMPANY TRADER JOE'S HARTFORD 1324191855/17/226/9/22Completed
132668051066OilR/S266805_1066TRADER JOE'S COMPANY TRADER JOE'S HARTFORD 1324191855/17/226/9/22Scheduled
142668051067OilRNS266805_1067TRADER JOE'S COMPANY TRADER JOE'S HARTFORD 1324191855/17/226/9/22Processing
152668031065Past266803_1065TRADER JOE'S COMPANY TRADER JOE'S / WCD LACEY DRY 5103 1324191845/17/226/10/22Completed
162668031066OilR/S266803_1066TRADER JOE'S COMPANY TRADER JOE'S / WCD LACEY DRY 5103 1324191845/17/226/10/22Scheduled
172668031067OilRNS266803_1067TRADER JOE'S COMPANY TRADER JOE'S / WCD LACEY DRY 5103 1324191845/17/226/10/22Scheduled
182668351831Past266835_1831NEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Completed
192668353570Dry266835_3570NEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Completed
202668358630OilR/S266835_8630NEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Scheduled
212668358730OilRNS266835_8730NEWARK NUT COMPANY NEWARK NUT COMPANY 10707025/20/226/15/22Scheduled
222669291066OilR/S266929_1066TRADER JOE'S COMPANY TRADER JOE'S / WCD CHINO 5043 1326127636/2/226/23/22Scheduled
232669291067OilRNS266929_1067TRADER JOE'S COMPANY TRADER JOE'S / WCD CHINO 5043 1326127636/2/226/23/22Scheduled
242669311065Past266931_1065TRADER JOE'S COMPANY TRADER JOE'S / WCD STOCKTON 5053 1326127646/2/226/23/22Completed
252669311066OilR/S266931_1066TRADER JOE'S COMPANY TRADER JOE'S / WCD STOCKTON 5053 1326127646/2/226/23/22Scheduled
262669311067OilRNS266931_1067TRADER JOE'S COMPANY TRADER JOE'S / WCD STOCKTON 5053 1326127646/2/226/23/22Scheduled
272668672619Past266867_2619SPECIALTY COMMODITIES SPECIALTY COMMODITIES 10120755/25/226/24/22NA
282668762624Dry266876_2624SPECIALTY COMMODITIES SPECIALTY COMMODITIES 10120775/27/226/24/22NA
292668762624Dry266876_2624SPECIALTY COMMODITIES SPECIALTY COMMODITIES 10120775/27/226/24/22NA
302668762624Dry266876_2624SPECIALTY COMMODITIES SPECIALTY COMMODITIES 10120775/27/226/24/22NA
Summary
Cell Formulas
RangeFormula
A8:D123A8=SORT(SORT(FILTER(FILTER(OImport[[Order'#]:[Type]],OImport[Type]<>"",""),{1,0,0,1,0,0,0,1,0,0,0,0,1},""),1),3)
E8:E30E8=+IF(ISBLANK(A8),"",CONCATENATE(A8,"_",B8))
F8:F30F8=IF(XLOOKUP($A8,OImport[Order'#],OImport[Bill To Name],"")=0,"",XLOOKUP($A8,OImport[Order'#],OImport[Bill To Name],""))
G8:G30G8=IF(XLOOKUP($A8,OImport[Order'#],OImport[ShipTo],"")=0,"",XLOOKUP($A8,OImport[Order'#],OImport[ShipTo],""))
H8:H30H8=IF(XLOOKUP($A8,OImport[Order'#],OImport[PO'#],"")=0,"",XLOOKUP($A8,OImport[Order'#],OImport[PO'#],""))
I8:I30I8=IF(XLOOKUP($A8,OImport[Order'#],OImport[OrderDate],"")=0,"",XLOOKUP($A8,OImport[Order'#],OImport[OrderDate],""))
J8:J30J8=IF(XLOOKUP($A8,OImport[Order'#],OImport[ShipDate],"")=0,"",XLOOKUP($A8,OImport[Order'#],OImport[ShipDate],""))
K8:K30K8=IFNA(IFS($D8="OilRNS",VLOOKUP($A8,'OilRNS-Sched'!$B$21:$C$65,2,FALSE),$D8="OilR/S",VLOOKUP($A8,'OilR&S-Sched'!$B$21:$C$65,2,FALSE),$D8="Past",VLOOKUP($A8,'Past-Sched'!$B$27:$C$71,2,FALSE),$D8="Dry",VLOOKUP($A8,'Dry-Sched'!$B$23:$C$67,2,FALSE)),"NA")
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
8:400Expression=$A8<>$A9textNO
A8:A1048576Expression=A8=A7textNO
F8:F1048576Expression=A8=A7textNO
G8:G1048576Expression=A8=A7textNO
H8:H1048576Expression=A8=A7textNO
I8:I1048576Expression=A8=A7textNO
J8:J1048576Expression=A8=A7textNO


Tab named "OrderImport" - Also is a table named OImport
FargoPlan.xlsm
ABCDEFGHIJKLMNOP
5Order#LinePO#Item#Item DescriptionBillToIDBill To NameShipDateQuantityShipToIDShipToOrderDateTypePk WghtOrderUIDOrder Status
6266803-2132419184106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44722420T53908 TRADER JOE'S / WCD LACEY DRY 5103 44698Past24266803_1065Completed
7266804-2132419186106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44721280T53808 TRADER JOE'S / WCD IRVING 5303 44698Past24266804_1065Completed
8266805-2132419185106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44721560T53103 TRADER JOE'S HARTFORD 44698Past24266805_1065Completed
9266930-2132612765106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44739210T53909 TRADER JOE'S / WCD MINOOKA 5353 44714Past24266930_1065Completed
10266931-2132612764106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44735560T53905 TRADER JOE'S / WCD STOCKTON 5053 44714Past24266931_1065Completed
11266960-2132682907106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44743210T54901 TRADER JOE'S / WCD DAYTONA DRY 5653 44720Past24266960_1065Completed
12267025-2132860231106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44753490T53907 TRADER JOE'S NAZARETH / 5503 DRY 44734Past24267025_1065Scheduled
13267026-2132860243106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44755140T53808 TRADER JOE'S / WCD IRVING 5303 44734Past24267026_1065Scheduled
14267027-2132860238106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 4475770T53909 TRADER JOE'S / WCD MINOOKA 5353 44734Past24267027_1065Scheduled
15267028-2132860233106524/1# RAW KERNEL 350 T53901 TRADER JOE'S COMPANY 44753630T53103 TRADER JOE'S HARTFORD 44734Past24267028_1065New Past
16266803-1132419184106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44722700T53908 TRADER JOE'S / WCD LACEY DRY 5103 44698OilR/S24266803_1066Scheduled
17266804-1132419186106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44721350T53808 TRADER JOE'S / WCD IRVING 5303 44698OilR/S24266804_1066Scheduled
18266805-1132419185106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44721490T53103 TRADER JOE'S HARTFORD 44698OilR/S24266805_1066Scheduled
19266929-1132612763106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44735770T53902 TRADER JOE'S / WCD CHINO 5043 44714OilR/S24266929_1066Scheduled
20266930-1132612765106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44739420T53909 TRADER JOE'S / WCD MINOOKA 5353 44714OilR/S24266930_1066Scheduled
21266931-1132612764106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44735630T53905 TRADER JOE'S / WCD STOCKTON 5053 44714OilR/S24266931_1066Scheduled
22266960-1132682907106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44743420T54901 TRADER JOE'S / WCD DAYTONA DRY 5653 44720OilR/S24266960_1066Scheduled
23267025-1132860231106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44753560T53907 TRADER JOE'S NAZARETH / 5503 DRY 44734OilR/S24267025_1066Scheduled
24267026-1132860243106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44755420T53808 TRADER JOE'S / WCD IRVING 5303 44734OilR/S24267026_1066Scheduled
25267027-1132860238106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44757350T53909 TRADER JOE'S / WCD MINOOKA 5353 44734OilR/S24267027_1066Scheduled
26267028-1132860233106624/1# R&S KERNEL 350 T53901 TRADER JOE'S COMPANY 44753420T53103 TRADER JOE'S HARTFORD 44734OilR/S24267028_1066Scheduled
27266803-3132419184106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44722560T53908 TRADER JOE'S / WCD LACEY DRY 5103 44698OilRNS24266803_1067Scheduled
28266804-3132419186106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44721210T53808 TRADER JOE'S / WCD IRVING 5303 44698OilRNS24266804_1067Completed
29266805-3132419185106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44721630T53103 TRADER JOE'S HARTFORD 44698OilRNS24266805_1067Processing
30266929-2132612763106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44735910T53902 TRADER JOE'S / WCD CHINO 5043 44714OilRNS24266929_1067Scheduled
31266930-3132612765106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44739210T53909 TRADER JOE'S / WCD MINOOKA 5353 44714OilRNS24266930_1067Scheduled
32266931-3132612764106724/1# RNS KERNEL 350 T53901 TRADER JOE'S COMPANY 44735490T53905 TRADER JOE'S / WCD STOCKTON 5053 44714OilRNS24266931_1067Scheduled
OrderImport
Cell Formulas
RangeFormula
M6:M32M6=XLOOKUP([@[Item'#]],$W$6:$W$54,$Z$6:$Z$54,"")
N6:N32N6=XLOOKUP([@[Item'#]],$W$6:$W$54,$V$6:$V$54,"")
O6:O32O6=IF([@Type]<>"",CONCATENATE([@[Order'#]],"_",[@[Item'#]]),"")
P6:P32P6=+IF([@Type]="OilRNS",IFNA(VLOOKUP([@[Order'#]],'OilRNS-Sched'!$B$21:$C$1048576,2,FALSE),"New OilRNS"),IF([@Type]="OilR/S",IFNA(VLOOKUP([@[Order'#]],'OilR&S-Sched'!$B$21:$C$1048576,2,FALSE),"New OilR&S"),IF([@Type]="Past",IFNA(VLOOKUP([@[Order'#]],'Past-Sched'!$B$27:$C$1048576,2,FALSE),"New Past"),IF([@Type]="Dry",IFERROR(IFNA(VLOOKUP([@[Order'#]],'Dry-Sched'!$B$23:$C$1048576,2,"Dry"),"New Dry"),"In Dry Schedule - No Status"),"NOT FARGO"))))


Tab named OrderHistory - Also is a table named OHistory
FargoPlan.xlsm
ABCDEFGHIJKL
1Order#LinePO#Item#Item DescriptionBillToIDBill To NameShipDateQuantityShipToIDShipToOrderDate
2
3
4
5
6
7
8
9
OrderHistory



Essentially, once all of an order's items are marked as "Completed" in Summary, I want to type in the order number in the highlighted cell then click a button which runs a macro to clear orders with that order number. The macro would find all instances of the order number in OrderImport (/ table OImport), then would copy&paste rows with the order number (from columns A:L) into OrderHistory (/table OHistory). Lastly, the macro would go back into OrderImport (/table OImport) to delete those same rows it copy-pasted into OrderHistory.

Example: If you were to click the button which ran this macro and "Summary" G5 had 266931 as the 'Completed Order', it would go into OrderImport, copy-paste rows 10, 21, 32 into OrderHistory (except OrderImport columns M through P), then delete rows 10, 21, and 32 in OrderImport
Note: OrderImport actually has 500+ lines of orders, but I only used the first 35 for the example's sake.

Again, any help would be immensely appreciated- I have spent a few days struggling to figure out a solution manually and found this forum. You all seem extremely nice and helpful!
 
Upvote 0
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Summary" 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 an order number in G3 and press THE RETURN key. The appropriate data will be cut to the "OrderHistory" sheet. No buttons are necessary.

VBA Code: 
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
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With srcWS
        .Range("A1").AutoFilter 1, Target.Value
        .Range("A2", .Range("L" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Please try this revised version and ignore the previous one:
VBA Code: 
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
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
       With srcWS
        .Range("A1").AutoFilter 1, Target.Value
        .Range("A2", .Range("L" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Please try this revised version and ignore the previous one:
VBA Code: 
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
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
       With srcWS
        .Range("A1").AutoFilter 1, Target.Value
        .Range("A2", .Range("L" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
Click to expand...
Hi Mumps,
I really appreciate your response, however the code is giving me an error when I try to run the revised version.
When I enter in the six-digit order number and press enter, the code gives an error on line:
VBA Code: 
 .Range("A1").AutoFilter 1, Target.Value

The error says "Run-time error '1004': AutoFilter method of Range class failed"

Any fixes you can think of?
Once again, I'm beyond grateful for your willingness to help.
 
Upvote 0
Try:
VBA Code: 
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
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
       With srcWS
        .Range("A5").AutoFilter 1, Target.Value
        .Range("A6", .Range("L" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A5").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
mumps said:
Try:
VBA Code: 
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
    Set srcWS = Sheets("OrderImport")
    Set desWS = Sheets("OrderHistory")
    Application.ScreenUpdating = False
       With srcWS
        .Range("A5").AutoFilter 1, Target.Value
        .Range("A6", .Range("L" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A5").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
Click to expand...
Huh, now
VBA Code: 
.AutoFilter.Range.Offset(1).EntireRow.Delete

"Run time error '1004':
This won't work because it would move cells in a table on your worksheet."

At least it made it farther into the With, but the error seems to redundant since that's what you're trying to do.
Thank you for the continuous help, I am very grateful
 
Upvote 0
I tested the macro using the data you posted and it worked properly without any errors. Are you using the macro on different data?
 
Upvote 0
Ah, I think I see what the issue is. I have other data to the right of the OrderImport columns A:P. Is it possible to just delete the table rows of OImport (the table name, if that's useful) instead of the EntireRow so the data to the right of the table can remain?

Currently, the macro moved all of order "266835" to OrderHistory, but OrderImport shows the filtered list of 266835 with all other rows still hidden. I'm assuming this is because the delete portion didn't execute due to the other data to the right of my table, my apologies for not clarifying that earlier.

Hopefully its an easy fix to delete OImport rows instead of entire sheet rows- thanks once again Mumps.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
2 column lookup formula to reference new data
Replies
1
Views
179
awoohaw
awoohaw
M
  • Solved
Copy and paste multiple matching rows to a new sheet
Replies
6
Views
104
Multim
M
P
  • Question
VBA issue. runtime 91 - only occurring after change to Range?
Replies
6
Views
220
PTEOMARK
P
J
  • Question
Macro to load 10 files from different folder into different sheets in Master File
Replies
2
Views
145
joxyin
J
A
  • Question
Worksheet from a template using a master list but protecting existing worksheets
Replies
0
Views
238
Alfalfa166
A

Forum statistics

Threads
1,215,219
Messages
6,123,688
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top