VBA Excel: Refresh pivot tables & tables automatically

Luis Diaz
Luis Diaz used Ask the Experts™
on
Hello experts, I have the following procedure which allows me to refresh the various pivot tables I would like to add some enhancements:


Sub Refresh_Pivot()
Dim PT As pivotTable
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
For Each PT In WS.PivotTables
PT.PivotCache.Refresh
Next PT
Next WS
End Sub

Open in new window


1.      Run the procedure after I change a data or after a save
2.      Include in the procedure the refresh of the various tables in the workbook

Thank you very much for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Place the following codes on ThisWorkbook Module.
The first code "Workbook_SheetChange" will trigger the ThisWorkbook.RefreshAll if you change the data on any sheet in the workbook.
And the second code will trigger the ThisWorkbook.RefreshAll after you save the workbook.

ThisWorkbook.RefreshAll will refresh all data ranges and available pivot table reports in this workbook.


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Skip:
Application.EnableEvents = False
ThisWorkbook.RefreshAll
Skip:
Application.EnableEvents = True
End Sub

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
ThisWorkbook.RefreshAll
End Sub

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank you very much for your feedback.
Should I add those procedures inside modules, Sheets or ThisModules?
Luis DiazIT consultant

Author

Commented:
Ok, I added in ThisWorkbook and it works perfectly. Thank you again for your help!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome! Glad it worked as desired.
Thanks for the feedback.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial