Addiing Sales Data to a Pivot Table|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I am just a sales guy, so please be gentle....I am NOT an Excel guru!!

I receive data (in an Excel file which is attached to an email) on a daily basis, which I need to update into a data table I have created.  This data table is the source data for my pivot table.  The data I receive daily is for all of the days of the current month.  What I have been doing is:

1. When I receive the data for the first day of sales for the month, I delete the last months data and copy it into the data table I have for my “Current Month Sales Data” file. 
2. Then each successive day, I take the file that I receive which contains sales data for all days of the current month and do the following.
   1. Open the file
   2. Reformat (take out some “Merge and Center”, Eliminate double spacing in one field which messes up my VLOOKUP on that field.  I am learning how to do that with a PivotQuery….
   3. Then I filter the data to only have the last day’s data (the only day I don’t have in my Pivot table data table), copy it and paste it into the bottom my data table in the “Current Month Sales Data” file.
   4. Refresh my pivot tables that use the data.

I have attached (with Randomized fake data) the files that I referenced.  I took much data from the one file out, to accommodate file size restrictions.

I KNOW that I am not bringing the data into my file in the most efficient manner….BUT, I don’t know how to best accomplish this.  I do it every work day, so I want to automate it as much as I can….

A DIFFERENT QUESTION

I have another  file with the same data as the “Current Month” data, but for the last couple years.  I add the most recent month to the file just like I do for the days of the current month file.  My question is, would it be better to have multiple data tables, each on their own sheet, feeding my pivot table (one for each years worth of data – they each have  approximately 300,000 to 350,000 rows of data)?    If so, do I use “Data Model” for that???

Thanks very much for any help!!!!  Sorry to be such an amateur!!

Best Regards,

Ken