Refresh all Pivots in the Excel workbook
As a part of my work I have to make many complex reports with so many Pivot tables to represent the data into various formats. On an average the reports that I work with has at least four to five pivot tables in a single workbook. Pivots work great but you have to manually refresh all the pivots whenever you update the data. Well there is a feature in the excel that will auto-refresh all the pivots in your workbook each time you open it, if you have time to do that then it will work fine but personally I don't want to close and re-open the excel sheet to refresh the pivots.
I would like to share this Macro that I wrote sometime back that will do your job. You can assign this macro to a Keyboard shortcut or create a custom button.
Sub RefreshAllPivots() ' ' RefreshPivot Macro ' Macro created by Ravi Sagar ' ''Call it from workbook_activate event Dim pt As PivotTable ' Variable to store the Pivots in the workbook Dim ws As Worksheet ' Variable to store the worksheets in the workbook For Each ws In ActiveWorkbook.Worksheets ' we are iterating through all the worksheets in the active workbook For Each pt In ws.PivotTables ' Among the worksheets iterated in the outerloop we are checking for the pivots only pt.RefreshTable ' if there are any pivot then this line of code will refresh the first pivot stored ' in the variable pt Next pt ' Iterating to the next pivot Next ws ' Iterating to the next worksheet MsgBox "All Pivots Refreshed" ' Once both loops are executed then display a meesage to the user. End Sub
Just run this macro whenever you have done any changes in the data and the pivots will refresh in one go. It will also display a message box when the job is done.
I hope you like this post. If you want to get similar tips and articles directly in your mailbox then why don't you subscribe to our mailing list.
Have a wonderful day!