CODEWITHSUNDEEP
excelpivotexcel-2010pivot-table
Zoke
Zoke

Reputation: 43

Refresh all pivot tables in excel spreadsheet when data changes

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!

Upvotes: 1

Views: 4451

Answers (3)

Abhishek Singh
Abhishek Singh

Reputation: 187

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

Upvotes: 1

Anita Harris
Anita Harris

Reputation: 21

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

Upvotes: 2

Ben
Ben

Reputation: 489

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

Upvotes: 1

Related Questions