1

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!

braX
  • 11,506
  • 5
  • 20
  • 33
Automating_My_Life
  • 37
  • 5

1 Answers1

0

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
DKoontz
  • 177
  • 16
  • Thanks for reply! So I added in the Calculate, it seemed to partly update one of my PivotTables, but when i clicked the actual Refreah All button, I noticed a small section still updated. Is it a timing thing? Maybe can we extend the calculate time? There's anout 10k rows of data across columns A:S. Also thanks for the ScreenUpdating mention, I partially copied in the code, should have mentioned that. – Automating_My_Life Apr 21 '22 at 22:21
  • I found a similar question, would adding in this calculation state check work? https://stackoverflow.com/questions/11277034/wait-until-application-calculate-has-finished – DKoontz Apr 21 '22 at 22:55