How can I refresh all the pivot tables in my excel workbook with a macro?

Question

I have a workbook with 20 different pivot tables. Is there any easy way to find all the pivot tables and refresh them in VB?

Answer 1

Yes.

ThisWorkbook.RefreshAll

Or, if your Excel version is old enough,

dim w as worksheet, p as pivottable
for each w in thisworkbook.worksheets
  for each p in w.pivottables
    p.refreshtable
    p.update
  next
next

Answer 2

You have a PivotTables collection on a the VB Worksheet object. So, a quick loop like this will work:

Sub RefreshPivotTables()
    Dim pivotTable As PivotTable
    For Each pivotTable In ActiveSheet.PivotTables
        pivotTable.RefreshTable
    Next
End Sub

Notes from the trenches:

1. Remember to unprotect any protected sheets before updating the PivotTable.
2. Save often.
3. I'll think of more and update in due course... :)

Good luck!

Answer 3

This VBA code will refresh all pivot tables/charts in the workbook.

Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

    For Each WS In ThisWorkbook.Worksheets

        For Each PT In WS.PivotTables
          PT.RefreshTable
        Next PT

    Next WS

End Sub

Another non-programatic option is:

• Right click on each pivot table
• Select Table options
• Tick the 'Refresh on open' option.
• Click on the OK button

This will refresh the pivot table each time the workbook is opened.

Answer 4

There is a refresh all option in the Pivot Table tool bar. That is enough. Dont have to do anything else.

Press ctrl+alt+F5

Answer 5

but I need to press it twice to refresh the datasource and then the pivot tables.

Answer 6

Cool solutions!

See more solutions to Pivot Tables in Excel VBA: Refresh Pivot Tables - Atualizando Tabelas Dinâmicas

André Luiz Bernardes A&A - WORK, DON´T PLAY! [email protected]

Twitter: @officespecialis @brzexceldevelop @brzaccessdevel