Multiple PivotTables and Cache won't refresh

user_15411704 0
Viewed 58

I have 2 sheets where I paste data into daily. I have premade PivotTables (source data is columns A:S).

After I paste my data, I run a macro but noticed the PivotTables don't refresh until I hit the 'Refresh All' button under the Data tab. Is my code missing something?

Application.ScreenUpdating = False

Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
Next pc

ThisWorkbook.RefreshAll

So I thought I need to refresh the PivotCache first of course, and it goes through all PivotCaches, and then RefreshAll to refresh the actual tables.

Not sure what I'm missing. Any help is appreciated, thanks!

pivot-table vba excel
1 Answers

I struggled with this same issue. Try adding a simple "Calculate" before the .RefreshAll. You also want to make sure to turn ScreenUpdating back on.

Application.ScreenUpdating = False

Dim pc As PivotCache
For Each pc In ThisWorkbook.PivotCaches
    pc.Refresh
Next pc

Calculate
ThisWorkbook.RefreshAll

Application.ScreenUpdating = True
user_165632860
Related