The Changing Source Data Dilema!

Hi,

I'm looking to find a way to by-pass having to change my source data everytime new Data is entered in my workbook.

But let me give you some background on how I run my process daily.

1. I open a workbook and I drop new data into a sheet -the sheet has vlookups on the fresh data- and the pivot table for all this data is located on another sheet in the same workbook. and the workbook is named 5/29/2015.

Next Day...
2. on 5/30/2015, There is new data available which is always longer than the previous days data and I take the 5/29/2015 workbook , drop in the new data and go back to manually changing the source data to capture the new values and info for 5/30/2015. Then I save it down as 5/30/2015.

So basically I want to automate this process to pull the current days data and drop it into the sheet and have the pivot table refresh automatically.

Here are the issues and obstacles I've come across...

Issues:
1.If I set a fixed range in the Pivot Table (To account for maybe all the rows in the spreadsheet) , when I refresh the Pivot the format of the pivot table changes and it does not reflect the original format...

2. Related to the first issue, I changed the Pivot data to account for just the columns being used and the same issue arises when the pivot is refreshed. Format of the pivot table changes.

3. I researched and found a Counta formula (which I forget how to use) that might be able to count the rows of data to include -Does anyone know what I'm referring to?

4. The data that I get is from another workbook and before I copy that data into the main workbook ( I clear the data from the main-workbook) after copying the data from the external source, I always get a message about connections or something and I ignore that and paste the new days data into the main workbook. The main workbook should have no connections to the outside workbook which contains the new data I am dropping in.

5. The whole pivot.cache refresh doesn't work with VBA because because just like in my previous points, the format of the table and the information being displayed changes. (Some fields/info dissapear).

Does anyone know a possible solution?

If I understand it correctly then using an Excel table to drop the fresh in would solve issues 1, 2 and 3.
Issue 4 should be solved by pasting (guess that's what you mean by dropping the data as value.
Maybe this will also solve issue 5.
If you have still problems pls upload yr workbook.