Forum Discussion
Change pivot table cache
Hi Everyone,
A couple of years ago I found in a different forum, that you can change the cache ID from a pivot table, you just need 2 functions to do so and I need your help to find what these are.
The purpose is to change the source cache from one pivot table and replace it with the cache from another pivot table in the same excel file.
I remember you had to click on one pivot table, then open visual basic with ALT+F11 then CTRL+G and write someting like
?ActiveWorkbook.XXXXX
This would return the Cache # (1, 2, 3, 4, whatever) of the pivot table.
Next step would be to click on the pivot table I want to chance source and go back to Visual Basic and write the second fuction that I don't recall
ActiveWorkbook.XXXXX but at the end yo include the cache # from the previous consult since this would override the cache #
I hope this enough information, It's clear that I have very basic VBA knowledge, please help me getting these two pieces I'll make sure I won't loose them again!
1. Refresh the pivot table: Select the pivot table.
Click Analyze > Refresh.
If the data source has been changed, the cache will be updated automatically after refreshing.
2. To change the data source: Select the pivot table.
Click “Analyze” > “Change Data Source”.
Select the new data source range and click “OK”.
3. Clear Old Cache: Select the pivot table.
Click Analyze > Options > Data > Clear Old Cache.
4. Use VBA Script: Press Alt + F11 to open the VBA editor.
Insert a new module and enter the following code:
vba
Sub ChangePivotCache()
Dim ws As Worksheet
Dim pt As PivotTable
Dim pc As PivotCache
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Set pt = ws.PivotTables(“PivotTable1”)
Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=“NewRange”)
pt.ChangePivotCache pc
pt.RefreshTable
End Sub
Run the script to change the pivot table cache and refresh it.
5. Contact Microsoft Support: If none of the above methods work, it is recommended to contact Microsoft support team with detailed error messages and screenshots for further assistance.
