Excel Pivot Tables do not automatically update when the source data is changed, they have to be refreshed. To make your Pivot Tables refresh automatically you need a macro to do the refreshing and an Event to trigger the macro. This seems quite an involved process the first time that you do it but it gets easier, it is mainly just copying from one workspace to another.

Recording the Macro
You have to record a macro and then copy the recorded code from the macro into one of the worksheet’s events. Firstly, the recording: Select a cell in your Pivot Table and then choose Tools, Macro, Record New Macro from the menu. Just 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, so choose the Data menu and select Refresh Data. The recording is completed and you can turn off the Macro Recorder by choosing Tools, Macro, Stop Recording. The next job is to find the recorded code and copy the instruction that refreshes the Pivot Table. Choose Tools, Macro, Macros and select the recorded macro from the list and then click the Edit button on the right hand side. You will now see the Visual Basic Editor displayed and you can view the recorded procedure, which should look something like this:

Sub Macro1()
' Macro1 Macro
' Macro recorded by Anon E. Mouse
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Copy the line of code that refreshes the table, it should be obvious which one you need. Do not copy anything else. Now, return to the Excel worksheet workspace by clicking an Excel icon or by pressing ALT-F11.

Using an Event to trigger the Macro
Next, you need to find the Event code shells for the worksheet where your Pivot table is located. Right-click the worksheet tab for the relevant worksheet and choose View Code from the shortcut menu. You are returned to the Visual Basic Editor once again. Looking at the active window you will see two drop-down lists at the top of the window.

The drop-down list on the left hand side is the Object list, the one on the right hand side is the Procedure list. Drop the Object list and select the Worksheet object, then choose a suitable event from the Procedure list. The Activate event is probably the most suitable for our purposes. Paste your recording into the procedure so that it looks like this:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

The Activate event is triggered whenever you make the worksheet active, so in order to test that the procedure is working correctly you need to perform the event. Return to the Excel worksheet workspace by clicking an Excel icon or by pressing ALT-F11, select another worksheet in the workbook and then select the original worksheet where the Pivot Tables are stored. This triggers the event and you will see that the tables are automatically refreshed. Of course, you do not have to make the initial macro recording every time that you need to do this, 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 described 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 Table Options from the short cut menu. The name is shown in the top left corner of the dialog. It is quite in order to list the Pivot Tables if you have more than one which needs refreshing in which case the procedure would be like this:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Although it would be easier to have a looping procedure so that you can avoid having to look up the name of each individual Pivot Table:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.PivotCache.Refresh
Next
End Sub

Pivot Tables are contained by each individual worksheet so if you want to have a procedure which will refresh every Pivot Table on every worksheet in the entire workbook then you will need an outer loop for the worksheets and an inner loop to refresh the pivot tables. The code would be as follows:

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.Refresh
Next pt
Next ws
End Sub

If you like the code and live in London try one of our Excel VBA Classes if not they download our manual on Excel VBA