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.

Please Login or Register  to view this content.

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.