Change Pivot Cache

**karinos57** · 03-17-2009, 06:47 PM

In the past I have tried this but did not get help but I hope this time I get some kind of help because I am dying for help now. I have several pivot tables in my workbook with one data source. The data is extremely huge so I pulled the data from Access Database using EXTERNAL DATA SOURCE method in excel. So now I have my huge data in the pivot cache; what I would like to see is when user selects a Region from the pivot table, I want the user to be able to save the excel workbook with the Region he/she selected only. For instance, if the user selects East region, I want them to be able to save the whole workbook with East data only and exclude the rest. I have researched online and found some codes but could not figure out how to utilize it. I have attached small sample file for more details. Below is code I found on internet and it may be helpful to you. Thank in advance for your help
link: http://www.contextures.com/xlPivot11.html

Please Login or Register  to view this content.

**Ron Coderre** · 03-17-2009, 07:44 PM

You can't manipulate the actual pivotcache data.
But....maybe this is an option...

If you double-click a pivot table calculated value (from the far right column
of the pivot table), Excel will insert a new sheet in the workbook that
contains all of the data behind the dbl-clicked value.

Using your posted example:
Double-click the Grand Total for East.
Excel inserts a new sheet with 65501 rows of backup data.
Then you (or your user) could move that sheet into a new workbook and
create a new pivot table based on that sheet.

Is that something you can work with?

**karinos57** · 03-17-2009, 08:03 PM

thanks for your response. But the problem with that is that some of my region have more than 70,000 rows, so that is why i am using a pivot table just to control the number of rows. thanks.