Goal: A macro to allow Excel users to dynamically update a change source data range in a Pivot Table.

This tutorial will explore one way to creating a macro-based solution for managing dynamic source data ranges in a Pivot Table. As we can see we have a simple data array of business units by month by sales in Sheet1, which is referencing a pivot table in Sheet2.

The challenge of managing dynamic ranges was covered in an earlier post.

However, how can we achieve automation to reflect a changing source data range? What happens if we add some more financial data?

A standard source data range like in this Pivot Table will be unable to update for this expanded source data range. We can evidence this when we try to refresh the data.

Hence we need to write a macro to achieve this.

Writing the macro

Remember to insert a module. Notate your macro with a brief description. I will call this macro, dynamicPTRange

Firstly we will disable these following Excel properties, in order to improve the macro’s performance. Next, we need to declare the object variables of the macro. Some of these basic learnings were covered in the earlier video, Excel VBA Dynamic Ranges.

It is vital to accurately declare these object variables, otherwise the dynamic PivotTable range macro might not work properly. Similar to the dynamic ranges macro video, we need to set certain objects – as in the above screenshot.

First our two worksheets, Sheet1 containing the source data and Sheet2 with the destination PivotTable. It is wise to fix the name of the PivotTable to “PivotTable1”.

“startCell” needs to be explicitly stated too. The source data clearly begins in cell address “A1”.

Completing the Dynamic Ranges Pivot Table macro

Next the macro must identify the last row and last column in the source data range.

Now we have identified the last row and last column in the source data, we can apply it to the rngPivot object variable. This next piece of code will be applied into the Pivot Table as the dynamic source data range.

It is always a good approach in a macro to add an Error Handler, as it will help to minimize the chance of macro errors. This error handler will stop the macro from proceeding if one of the headings is blank. Whenever writing in a MsgBox into a macro, it is vital to ensure it is 100% accurate, otherwise you might incur a macro error.

This final part of the code will update or amend the Pivot Table’s source data range.

It does this in part by the use of the ChangePivotCache syntax. Then refreshing the Pivot Table with this piece of code. Next we can re-enable the Excel properties, which were earlier disabled because our macro is complete.

Let’s now review the macro.

Running the macro

We are ready to run the macro, by adding some financial data. As we can see the macro is correctly updating the Pivot Table based on the dynamically changing source data.

How about if we want to delete some of this source data? The macro is correctly making this update in the Pivot Table.

Here is the example workbook, which contains the Dynamic Ranges in a Pivot Table macro.

Download Spreadsheet Vault SpreadsheetVault – Dynamic PivotTable Range (solution).xlsb