redefining the SourceData of a PivotCache

[lalbatros]

Hello,

I am reorganizing a workbook containing 60 pivottables based on 7 pivotcaches.
I need to redefine the SourceData of these pivotcaches.
Would you know how to do that?

I have already seen that for pivottables this should be done like this:

pt.PivotTableWizard SourceType:=xlDatabase, SourceData:=srcd

However, I do not want to do this modification on a per-pivotable basis.
This is to make sure the number of pivotcaches does not increase during that process.
Keeping only a small number of pivotcaches is very useful for the performance of this workbook.

Any suggestion would be welcome.

Thanks,

Michel

 

[RoryA]

See if this page helps.

 

[lalbatros]

Thanks rorya.

Unfortunately it doesn't help.
I do not connect PTs to databases anymore, since long.
Instead, I download the source data, without even using any QT: I do it myself.
In this way I could improve performance and I am more in control.

Therefore, I am dealing with PT connected to excel ranges.
Today, I want to change the connection from an external workbook to the active workbook.
The active workbook contains the same data, and I want to connect all PT to these data using only the pivot caches.

This is a one-shot problem in the conversion job I am doing now.
Unfortunately, I don't not see other easy ways to do that, except than writing a short VBA sub.
But I found out I am not able to write this short VBA sub !

More suggestions?

Thanks,

Michel