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

Change the Pivot Cache

'If you have created several pivot tables in a workbook, you may find it more 'fficient to use the same pivot cache for all the pivot tables. The following code will change the pivot cache for each pivot table in the workbook. 


Sub ChangePivotCache()
'change pivot cache for all pivot tables in workbook
Dim pt As PivotTable
Dim wks As Worksheet

  For Each wks In ActiveWorkbook.Worksheets
    For Each pt In wks.PivotTables
        pt.CacheIndex = Sheets("Pivot").PivotTables(1).CacheIndex
    Next pt
  Next wks

End Sub

**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.