Excel – Make Pivot Tables Refresh Automatically

Microsoft Excel - Make Pivot Tables Refresh Automatically

Unlike formulas, Excel Pivot Tables do not automatically update when their source data is changed, they have to be refreshed. That’s no problem if you can remember to do it-but it’s all too easy to forget.

To make your Pivot Tables refresh automatically you need a Macro to do the refreshing and an Event to trigger the macro. You record a macro of your refreshing the Pivot Table and then you copy the recorded code and paste it into one of the worksheet’s events. This seems quite an involved process the first time you do it but it gets easier every time as it is just copying and pasting from one workspace to another.

 

Recording the Macro

Recording a Macro

Firstly, the recording: Select a cell in your Pivot Table and then click the Record Macro control in the Macros group at the extreme right of the View tab.

Make sure that the Store macro in: setting is This Workbook and click the OK button. Now you have to record your refreshing the Pivot Table; just right-click the active cell and choose Refresh from the shortcut menu. The recording is now completed and you have to turn off the macro recorder by clicking the Stop Recording control in the Macros group on the View tab.

The next job is to find the recorded code and copy the instruction that refreshes your Pivot Table. Click the View Macros control in the Macrosgroup on the View tab, select your recorded macro from the list and then click the Edit button on the right hand side. You will now see your recording displayed in the Visual Basic Editor, it should look something like this:

 
A Macro to Refresh a Pivot Table
Copy the line of code that refreshes the table, it should be obvious which one you need-the line is highlighted in the illustration above. Do not copy anything else.
 

Now, exit the Visual Basic Editor and return to Excel by clicking an Excel icon or by pressing ALT+F11.

 

Using an Event to trigger the Macro

Right-click the tab

Next, you need to find the Events for the worksheet where your Pivot Table is located. Every Excel worksheet has a collection of event-driven macros which trigger automatically whenever a particular event occurs. For example, the Selection Change event is triggered whenever you click a cell on the worksheet. We are going to use the Activate event to make our Pivot Table refresh.

Right-click the worksheet tab and choose View Code from the shortcut menu. You are returned to the Visual Basic Editor once again but this time the screen looks blank. Don’t let that put you off, looking at the top of the active window you will see two drop-down lists.

 

The drop-down list on the left hand side is the Object list, the one on the right hand side is the Procedure list.

The Object list, on the left hand side

Firstly, you go to the Object list on the left and select the Worksheet object and then you will be able to choose a suitable event from the Procedure list on the right.

The Procedure list, on the right hand side

All objects have default events and these usually appear in the window automatically, ignore them and scroll up the list to select the Activate event. Paste your recording into the event procedure so that it looks like this:

Paste your recording into the Event

The Activate event is triggered whenever you make that worksheet active, so in order to test that the procedure is working correctly you need to perform the event.

Return to the Excel workspace by clicking an Excel icon or by pressing ALT+F11, select another worksheet in the workbook and then return to the original worksheet where the Pivot Table is stored. This triggers the event and you will see that the tables are automatically refreshed. The best test is to change something in your source data and then see if your Pivot Table updates to reflect the change.

Using your Macro Again

Of course, you do not have to do the macro recording every time, all you need is the code for the Refresh method so you can type it in or paste it from a previous recording. But, be careful, each Pivot Table needs to be correctly referenced using its Name Property, this is the text value inside the brackets. In the example below the name of the Pivot Table is “PivotTable1”
 
ActiveSheet.PivotTables(“PivotTable1”).PivotCache.Refresh
 

To find out the name of a specific Pivot Table, right-click one of its cells and choose Pivot Table Options from the short cut menu.

Recording Refresh All

If you have several Pivot Tables that need to be refreshed then it is quite in order to list the various Pivot Tables one after the other in your macro but it is probably best if you recorded Refresh All in which case the initial recording would like the illustration on the right.

Saving your file

When your Excel workbook file contains macros remember to save the file in the Excel Macro-Enabled Workbook format.

If you want to have your Pivot Table source data range updated automatically see Automatically change Excel Pivot Table source data range

 

 

 

 

Training Courses

If you want to find out more about Excel Pivot Tables or Macros then you might like to arrange an Excel training course for yourself or with some of your colleagues. It’s really easy to book one of our courses and they’re great value for money