Refresh all pivot tables in excel spreadsheet when data changes

1
votes

Does anyone know the VBA code to automatically refresh all Pivot tables (2 sheets that each hold 4 pivot tables) when data in the Excel (2010) spreadsheet is changed? It can also search for change in a specific cell.

Any advice is welcome!

3
excelpivotexcel-2010pivot-table

3 Answers

2
votes

If your pivot tables are set up with absolute ranges throughout a workbook, I'm a fan of the simple approach:

Sub RefreshAllPivots()
' RefreshAllPivots Macro
' Refresh all the pivots in the workbook
    ActiveWorkbook.RefreshAll
End Sub
1
votes

this code added to the worksheet containing the source data should work

Private Sub Worksheet_Change(ByVal Target As Range) Dim Sheet As Worksheet, Pivot As PivotTable For Each Sheet In ThisWorkbook.Worksheets For Each Pivot In Sheet.PivotTables Pivot.RefreshTable Pivot.Update Next Next End Sub

For more info check http://msdn.microsoft.com/en-us/library/office/ff839775(v=office.15).aspx

1
votes

If you have data in the sheet called "data" and table is in sheet "pivot" with table named "PivotTable1", you can paste this on your data sheet vba tab.

Private Sub Worksheet_Calculate()

    'If data on this worksheet changes, refresh the pivot table
    Sheets("Pivot").PivotTables("PivotTable1").RefreshTable

End Sub