VBA to change pivot source.

[pedie]

Hi, i have several excel pivot with excel Sheet1.Range("A1:U900") as data source. Now i have same data in access and want to switch the data source automatically using vba because i do not want to go and manually create all this pivot again. I have created a connection manually and now i am wanting to change the source to connection and remove the sheet which contain the data.

Thanks
Pedie

 

[pedie]

I just need to alter the cache index of the source. it worked.