Consolidate existing Pivot Tables to use one cache

All my pivots use the same exact data source. I tried to be good about copying and pasting pivots, but I'm afraid some pivots were not created that way.

I found this code on the web:

Please Login or Register  to view this content.

But this does not work. I'm using the sheet ALLRATES (has two pivot tables PivotTable1 and PivotTable2). I just want to make every pivot in my workbook use the same cache as PivotTable1 in the ALLRATES worksheet.

I had only 2 pivot caches in my workbook, but this code shaved 20MB off my data.

Open your workbook,
Save a copy (just in case something goes wrong)
Press ALT+F11
Double-click Module 1
Copy and paste code below
Replace the (“Glembo”) in: Set ptO = wb.Sheets("Glembo").PivotTables(1) with the name of a worksheet that has ONLY ONE PIVOT Table in it
Run macro ‘ConsolidatePivotCache’

This will tell you first how many pivot caches you currently have
Then the macro runs after you click ok
Then it will tell you how many pivot caches you have after running the macro
Then, all pivot tables will refresh.

Save file – watch the megabytes disappear!

Please Login or Register  to view this content.