Pivot help

You don't say how the data gets overwritten every day. Also I don't know what version of Excel you are using. Therefore, I propose the most generic solution: create a named dynamic range to cover the data and use that as the data source for the pivot table. That should work as long as the column headers don't change.

Assuming contiguous data and the data starts in Cell A1 the following should work. You'll need to use the name manager to assign a name to it.

=OFFSET('SheetName'!$A$1,0,0,COUNTA('SheetName'!$A:$A),COUNTA('SheetName'!1:1))

Change the data source to the name associated with this range. You may have to refresh the pivot table, but it should always point to the exact amount of data that you have.

Putting the code into Sheet AAA won't do any good since it gets overwritten every day.

So what you are going to have to do is import AAA into another workbook. There are a couple of ways to do this: (a) you can open the new workbook and copy and paste the data to where you want it using VBA. (b) You can use MS-Query which will bring the file into a table in the new workbook without opening it.

This is as far as I can take it without having sample workbooks.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.