Updating Pivot Tables Automatically

Excel Pivot tables are fantastic for creating fast and accurate, sorted, summary information in Excel. To update a pivot table, traditionally you have to update the source data and either right click on the pivot table and click on the Refresh Button or Click on the Refresh button at the top of the screen;

Either refresh by right clicking on the Pivot Table and the menu on the left will appear, click on refresh.  Or you could click on the PivotTable Tools and click the refresh button.

Either option leaves you open to the possibility that the pivot table will not be updated as the user has to remember to update the pivot table whenever the source data has been updated.  

Having an Excel pivot table update automatically is a valuable tool when dealing with data which changes on a regular basis.  The idea is to have a dataset populated and have the pivot table update to reflect this change.  There are a few ways you can do this.  The first method I will describe is to create a Dynamic Named Range in Excel.  Then in VBA add a Worksheet Activate Event which will Refresh the pivot table when you click on the worksheet.

Consider the following example data set. The data starts in Column A and always ends in Column E. I will call the dynamic range Source and the formula for the named range is;

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$20000),5)

The Column headers start in Row 1 of Column 1 (A1) I have used an upper bounds of the range of 20,000 rows. As I know my data will never go beyond this point. I have offset the columns by 5 which means the source data will be 5 columns wide. It will take the last used row in Column A and offset that by 5 Columns. Change to suit. If you don’t know how many columns are going to be in the pivot table use a dynamic formula like this.

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

If your data is based on a static range all you need to do now is change the source data to be equal to the dynamic range.

Now simply type in=Source

Note:  Source needs to be the name of your dynamic named range. 

The VBA Procedure

The following Excel VBA procedure needs to be placed in the Worksheet Object where the Pivot Table resides.

The Excel VBA code is as follows;

Option Explicit
Private Sub Worksheet_Activate() 'Excel VBA to refresh a pivot table. Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt
End Sub

To test the above VBA coding go to the bottom of your dataset and put a new line of unique data.  This is so you will be easily able to spot the update in your pivot table.  As you click on the pivot table sheet you should see that unique piece of data added to the summary table.  If you see that it works now go back and delete the line and when you click back into the pivot table sheet the data should be gone.

The same process can be achieved with VBA.  Create a regular named Range called Source1 and follow the same steps to connect your pivot table up to the named range. 
 
Now place the following VBA code into the Worksheet Object.

Option Explicit

Private Sub Worksheet_Activate()' Excel VBA to update pivot table automatically.
Dim sh As Worksheet
Dim pt As PivotTable Set sh=Sheet2

sh.Range("A10").CurrentRegion.Name="Source1"
For Each pt In ActiveSheet.PivotTables
pt.RefreshTable
Next pt

End Sub

Notice how the Source1 named range is updated directly in VBA. The name for this pivot table range is Source1.

Filtered a Pivot Table by an External List

For long lists inside a pivot table it might be an idea to have data in a separate list and simply loop through that list.  The Excel VBA to do so is as follows;

Option Explicit

Sub FilterPiv() 'Excel VBA to filter the Pivot Table by a list outside the pivot Table.
Dim i As Integer
Application.ScreenUpdating= False
Dim pi As PivotItem
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.PivotItems([G11].Value).Visible=True
For Each pi In .PivotItems
If pi.Name <> [G11].Value Then
pi.Visible=False
End If
Next

For i=12 To Range("G" & Rows.Count).End(xlUp).Row
.PivotItems(Range("G" & i).Value).Visible=True
Next i
End With
End Sub

The following Excel file outlines the VBA procedures above.