1

My excel file holds 30-40 pivot tables and its taking too long to update it all. Is there a way I can update it with just a push of a button using VBA macro?

It would be nice if I could assign a shortcut key for this macro to refresh all the pivots. One shot deal, maybe?

Community
  • 1
  • 1
  • possible duplicate of [How can I refresh all the pivot tables in my excel workbook with a macro?](http://stackoverflow.com/questions/70947/how-can-i-refresh-all-the-pivot-tables-in-my-excel-workbook-with-a-macro) –  Apr 03 '15 at 14:55

1 Answers1

0

In Excel 2007 and 2010 you can update them by pressing Ctrl+Alt+F5.
Ben Michael Oracion has posted the VBA code, what he didn't post is that if you want to run a single line of VBA code after the RefreshAll is triggered it may cause a bug. For this very reason I set the BackGroundQuery property to False beforehand.

Dim piv As PivotCache
For Each piv In ActiveWorkbook.PivotCaches 'beállítja hogy ne legyen háttérben frissítés, így nem fut bug-ra
    piv.BackgroundQuery = False
Next

ActiveWorkbook.RefreshAll 'Frissít minden pivotot (és táblát)
user3819867
  • 1,114
  • 1
  • 8
  • 18