Auto copy data based on multiple criteria from one sheet to a specific location in another

Thank you for your guidance. I'm attaching a revised file and a better explanation of the process and the expected results.

The data in SodaSales is mock data, but the structure of the workbook is identical to the workbook I'm using. I'll refer to my actual workbook as workbook1.

I'm using Excel 2010 MS Office 2010 Professional on a Windows platform.

I'm open to whatever solution will be most efficient.

My Process:

In SodaSales.xls the SA tab will ultimately be used for reporting purposes, a dashboard,etc.

In workbook1 the SA tab pulls data from two other tabs, SD and SD2. Both SD and SD2 are linked via data connection to their corresponding master files on the network, and I'll have the connection set to refresh when the file opens. I wanted to combine the two tabs into one, but my IT guy said there will be too many records to combine the two tabs. Since the existing records are not flagged, I don't know exactly how many new records are added each day/week/month.However, I did a manual sort on the two master files.
Here's what I see:
Master file one has 36,368 records so far this year. In this file there are 16,200 records for Jan and 2,300 records for February. Master file two only has about 12,233 records so far this year.

In workbook1 I copied the existing records from SD to SD2 and removed the duplicates. Then I inserted subtotals to get the weekly total by customer. The format of the SA Before tab looks the same in SodaSales. It just has much less records.

Expected results:
SA Before lists the records as of the end of the forth week of January. SA After has the new records for the last week of January pulled from SD and SD2. In SA After we can see that Costco has received a shipment of Vanilla Soda which is a brand new product, and they have also received a shipment of Raspberry Soda which some stores were already purchasing.The same is true for Target.

My dilemma is how do I get Excel to automatically copy the WAREHOUSE, SHIPTO_NUMBER, SHIPTO_NAME and PRODUCT_NUMBER fields for these new records as they updated into the SD and SD2 tabs?

Other fields on the SA tab:
The product description will be pulled from another tab using VLOOKUP because the descriptions in the master files are confusing.
The CASE_SIZE and SALESPERSON fields will be populated manually.
*Thanks to John* The total number cases per week listed in columns I through AF will be calculated using this formula:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&{"SD";"SD2"}&"'!F2:F100"),
INDIRECT("'"&{"SD";"SD2"}&"'!C2:C100"),$D7,
INDIRECT("'"&{"SD";"SD2"}&"'!A2:A100"),$B7,
INDIRECT("'"&{"SD";"SD2"}&"'!B2:B100"),$C7,
INDIRECT("'"&{"SD";"SD2"}&"'!E2:E100"),$A7,
INDIRECT("'"&{"SD";"SD2"}&"'!D2:D100"),$E7&"*",
INDIRECT("'"&{"SD";"SD2"}&"'!g2:g100"),YEAR(I$6)&"*"&I$5))

I have seen code that copys records, but I'm not familiar enough with VB to piece it together for this scenario or savvy enough to know if there's a better solution. I'm eager to learn and very appreciative of any help you have to offer!