macro script to collect pivot data

I am trying to geta a script happening that will do the following.

I have a daily spreadsheet that gets emailed around with a pivot table on it. the pivot table is always changing size so the "Total" cell will not always be in the same column, nor row. For one, I need the script to drill down to the background data from the pivot table and paste back into the active sheet from the original work book in which the script is ran.

So.....

1. My main worksheet is open (workbook A)

2. I open the pivot table workbook (Pivot Book A) from the email (which all characters in the name of the workbook are the same, except for the last two, i.e. 55, 56, 57, which are representative of the report number.

3. I switch back to "Workbook A" and run the macro/script

4. The data is then extracted from the pivot table. (pivot Book A) of which the tab/page in the pivot workbook is always named "BRIS RDC FCST SCHEDULE SPLIT"

5. The data sheet which opens from the pivot table will expand from column "A" to column "L".

6. These then need to be copied as columns

7. The copied data then needs to go back to the active spreadsheet and be pasted in tab/page "Data Sheet" in columns "E" to "P".




any help I can be provided with would be much appreciated. I am desperate to get this happenning.

Please edit the title of your post to be descriptive of your problem.

Edit: Thank you.