Pivot caches?

[Huyeote]

Someone passed me a workbook which contains many pivot tables on
separated sheets. After examining those pvt, I found they all point to
exactly same data source but have different pivot cache, which makes
the workbook unnecesssarily large (10MB). My question: how can I assign
all pvt to just one single pivot cache to save space without start
building pvts all over again?

Thanks.

Huyeote

 

[sirin]

You need to change the referance of all pvt except the first to th
first pvt table. That will reduce the size of file also.
While creating the pivot table the first step in the wizard is t
choose the base data with 4 options. Choose last one ("Another pivo
table or pivot chart" for all the subsequest pvt using the same dat
range.
Thanks,
Sirin.

 

[keepITcool]

try:

Sub AllToCahceOne()
Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
If pt.CacheIndex <> 1 Then pt.CacheIndex = 1
Next
Next

MsgBox "Number of PivotCaches: " & _
ActiveWorkbook.PivotCaches.Count

End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >