How to make existing pivot tables all use same pivot cache in VBA

[chubbs504]

I have this code here that I found online to make every pivot table in the workbook use the same pivot cache. However, I want to make every pivot table on only a specific sheet use the same pivot cache. How can I modify this code (or just produce new code) so that the macro only changes the pivot tables in one sheet instead of the whole workbook? I have very little experience with vba.


Sub ChangePivotCache()

Dim pt As PivotTable
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
For Each pt In wks.PivotTables
pt.CacheIndex = Sheets("Sales Summary").PivotTables(1).CacheIndex
Next pt
Next wks

End Sub