Autorefresh Pivot tables

[Caravishah]

Hi,

I need to auto update/refresh all the pivot tables in my workbook. i.e. I do not want to go and press refresh/refresh all. Every time the source data changes it should update the pivot automatically. I did find some VBAs online but they were working only when I went to the sheet that had pivot. i.e. I had to activate that sheet (be on the sheet) for the VBA to run.

I do not want to have any further steps like going to the pivot sheet or hitting the macro button, etc. I want excel to consider the pivot just as a linked formula to the source data and update when I press F9 (workbook is on manual mode).

Is there a way out? Any ideas?

Thanks very much.

 

[JAMIR]

Hello Caravishah

try following code in you Worksheet VBA Screen

Private Sub Worksheet_Activate()
'If this worksheet is activated, refresh the pivot table
'Change "Pivot" to your sheet's name
'Change "PivotTable1" to your pivot table's name

Sheets("Pivot").PivotTables("PivotTable1").RefreshTable

End Sub

Regards,


Zameer Shaikh

 

[Caravishah]

Hello Caravishah

try following code in you Worksheet VBA Screen

Private Sub Worksheet_Activate()
'If this worksheet is activated, refresh the pivot table
'Change "Pivot" to your sheet's name
'Change "PivotTable1" to your pivot table's name

Sheets("Pivot").PivotTables("PivotTable1").RefreshTable

End Sub

Regards,


Zameer Shaikh

Thanks Zameer. Are you sure it will work without going to the active sheet? I want it to update without running the macro button or without going to the pivot sheet.

 

[Chihiro]

There are couple of ways to do it but will depend on how your source data is being updated and how your workbook/sheets & pivots are structured.

1. Tie it to Worksheet_Change event on source data. If source data is updated manually.

2. If source data is updated by code. Add line to refresh Pivot at the end of the code.

Code will depend on version of Excel you are using (Excel 2013 & later has Workbook.RefreshAll method).