Change pivot cache

[sh1ne]

Got a little problem, want to make a file way smaller in memory. I want to reduce the cache of pivots. How can I do that?

Currently I'm havin' a macro like this:

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= "'[" & sourceofpivots.Name & "]Data'!R1C1:R" & lastrow & "C79"
 ActiveWorkbook.ShowPivotTableFieldList = True

This code is running in every sheet(15x) of file, when we count cache's which are the same(every pivot have same source) and increasing size of file dramatically.


Ive heard that I can reduce size of file by using "pivot cache" or something like this in macro. Someone could help me by showing correct way or maybe help with modernize this code?


Thanks

 

[arthurbr]

Does this help ? http://www.contextures.com/xlPivot11.html