Solved: Pivot Cache Consolidation

[Phelony]

Hi Guys

I've got a spreadsheet that's loaded with pivot tables from two mdb files. In it's construction, which was a little timebound, not all of the pivot tables were told to use the same source data even though they were.

This has created a file that's 203mb in size and as the source data grows that's only going to get worse exponentially.

I've got the below code to force them to use the same source data:

[VBA]Sub PTReduceSize()
Dim wks As Worksheet
Dim PT As PivotTable
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
For Each PT In wks.PivotTables
PT.RefreshTable
PT.CacheIndex = 1
PT.SaveData = False
Next
Next
End Sub[/VBA]

(The On Error Resume Next) was the only way to get around the pivot overlap issue I could find. I know it's an evil line to use!

PROBLEM: This code is forcing all of the pivot tables to use the first load of pivot data but applying to to pivot tables where that dataset is not valid.

QUESTION: Is there a way to change the above to get it to recognise that pivot tables A, B and D use one dataset while pivot table C uses a different source data?

I admit that I've pinched the original code from the web, but I've tried a few different variations and ideas to get it to behave the way I want without success.

Any input would be greatly apprecaited.

Thanks

Phel x

PS - Yes, I'm about to go and mark my other threads "Solved", sorry for the delay in doing so.

[Phelony]

Ignore the above...I was having a blonde moment.

All of the pivots that require the use of the same source data are on the same worksheets, it's that darned activesheet command I insist on using everywhere that's messing it up!!!