I have a file with two tabs, “data” and “pivots”. The data is updated daily and so the range of the pivot cache is not static. Various pivots get updated from the data. I tried to write the below in order to create a macro that will update the pivot cache and then run a loop to update each pivot:

Sub UpdatePivots ()

'Turn off screen updating
Application.ScreenUpdating = False

'Declare dimensions
Dim WS As Worksheet
Dim WSD As Worksheet
Dim PT As PivotTable
Dim PTC As PivotCache
Dim WB As Workbook
Dim Rng As Range

'Update the pivot cache
Set WB = ActiveWorkbook
Set WSD = WB.Worksheets("Data")
Set Rng = WSD.Range("A1").CurrentRegion
Set PTC = WB.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Rng, Version:=8)

'Use loop to cycle through pivots and update with new cache
For Each WS in WB.Worksheets
    For Each PT In WS.PivotTables
         PT.PivotCache.Refresh
    Next PT
Next WS

'Turn on screen updating
Application.ScreenUpdating = True

End Sub

Currently I am not interested in making the data tab a table. I also added a MsgBox after setting the Rng as well as after the loop and always see that the active sheet is “pivots”. So, I am obviously missing the way to actually update the cache which would be on “data”.

I have tried to change the parameters of the PTC code, but get errors because I don’t know what I am doing 🙂