VBA/Macro to copy data from 1 sheet to another based on values in a specific column

Hi! I am extremely new to the VBA/Macro world. I need to write some code to help me with a daily report that I turn in to all of the higher ups at my company.

Sheet 1:ShippedOrders will have a list of our orders that shipped out the day before, column I. Each order has as many rows as it does product types that shipped. So if order 123 had 4 different product, the order # is listed for 4 rows.
Sheet 2: Report will have a formatted report that I download from other software. While it should only have the order information that shipped the day before, there are always additional orders that get mixed in for various reasons.
Sheet 3: Deletions is a list of items that need to be deleted off of Sheet 2: Report before data is sent to Sheet 1: ShippedOrders.


1st-I need to eliminate all rows that contain any of the phrases on Sheet 3:Deletions via command button possibly.

2nd-I need a command button(?) that will have Excel look at the orders in column I (Sheet 1: ShippedOrders) and pull that many rows of information from Sheet 2:Report in the order that column I is already in.

I have attached a file to give you an idea of what I am working with, small scale.

I really appreciate any help that I can get. Doing this manually takes up so much of my work day that it is hard to keep up with all of my other responsibilities.


Also: I have been readying up on VBA BUT I am having the hardest time finding a well rounded reference on VBA terminology.

See the attached file with the codes embedded into the Command Buttons.

If I needed to add more items to Sheet 3: Deletions, would that be a fairly easy task for a beginner?

Code is set up to find the last row for each sheet so that should not be an issue.

I noticed that Transfer Orders button is pulling the allotted number of rows but, it is making say all 4 rows the same data instead of unique rows?

That is because you only have a single line of data for the order in the sheet that we are copying from. Is your workbook a realistic representation of your actual workbook? If yes, then what should the other three rows appear as if not a replication?

I understand. I will look at this a bit later. Need to run out for a couple of hours and no time now. I will need to revamp some of the code but it will take a bit.

Ok. I've taken a different approach to this issue. The first Command button stays. The second command button should be disregarded. I have provided a Power Query Solution. Power Query is part of Excel starting with Version 2010. Since you are running 2013, you will need to download the add-in available from Microsoft. Here is the Mcode for the solution.

First I loaded each of your tabs into Power Query and then merged the two tables based upon the SO Order.

let
    Source = Table.NestedJoin(Table1, {"SO Order#"}, Report, {"SO Order#"}, "Report!_FilterDatabase", JoinKind.LeftOuter),
    #"Expanded Report!_FilterDatabase" = Table.ExpandTableColumn(Source, "Report!_FilterDatabase", {"Sold To Customer Name", "Shipped To", "Item Description", "PO#", "LotNo/Roll/Batch", "Carton or Size", "Shipper", "Pkg ID/Tracking#", "SO Order#"}, {"Sold To Customer Name", "Shipped To", "Item Description", "PO#", "LotNo/Roll/Batch", "Carton or Size", "Shipper", "Pkg ID/Tracking#", "SO Order#.1"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Report!_FilterDatabase", {"Item Description"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Sold To Customer Name] <> null))
in
    #"Filtered Rows"

If you are unfamiliar with PQ, then look at the two links in my signature in Post #2 for a complete explanation and how to employ this. It is very fast and fairly simple to learn. Your file with the PQ solution is attached.