PivotTables require a refresh if the underlying data is changed or added to.  This tutorial will explain how to automatically refresh your PivotTable using a few lines of VBA code. The solution will work when you change existing data or add new data.  Follow the tutorial and/or watch the video below.

For ease of demonstration I am going to use a simple database as the source for a PivotTable – see below.

Data

Step 1: Convert Data into an Excel Table

The first step is to convert your data into an Excel table.  Excel tables automatically expand as you add new records, so provide an ideal data source for a PivotTable. You can perform this step before or after you have created your PivotTable.

To convert your data, select a single cell with in it and use the shortcut key CTRL T, then click OK in the Create Table dialog box to confirm.

An Excel table applies formatting to your data as shown below.

Step 2: Name Your PivotTable

Naming your PivotTable makes it easier to refer to in the VBA code.  On the PivotTable’s Analyze ribbon, you will find the option to name it – see below.

Step 3: Create the VBA Code to Automatically Refresh Your PivotTable

To create the VBA code, start by opening the Visual Basic Editor (VBE).  Use the shortcut key ALT F11 to do this.  In the VBE look for a list of open Workbooks (AKA VBA Projects) on the left side.  If it isn’t visible use the shortcut key CRTL R to display it.

Click the expand button next to the workbook containing your data. Then expand the Microsoft Excel Objects folder.

Within the objects folder is list of all the sheets in your workbook.  Double-click the sheet icon for the worksheet that contains your Excel table.  This will display a code window on the right.

In the code window change the menu item top-left from General to Worksheet.

Two lines of code will automatically appear in your code window.

If your PivotTable is on the same page as your Excel Table, add this line of code between the two existing lines (replace Scores with the name you have given your PivotTable):

If your PivotTable is on a separate sheet, you will need to specify the sheet name within the code, for example…

where the PivotTable is on a Worksheet called Sheet2.

Step 4: Save Your Workbook as a Macro-Enabled Workbook

Save your workbook as a Macro-Enabled workbook rather than an Excel Workbook – if you don’t your code will not work.  The option is available in the Save as type menu when you go to save – see below.

That’s it.  Your PivotTable will automatically update if your change or add data to your Excel table.

Watch our video of this tutorial…

Posted by Chester Tugwell