Welcome we are going to 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.
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.
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 like this.
First our two worksheets, Sheet1 containing the
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".
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
PivotTable 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
minimise 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 PivotTable's source data range.
It does this in part by the use of the
ChangePivotCache syntax
Then refreshing the PivotTable
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.
We are ready to run the macro, by
adding some financial data.
As we can see the macro is correctly
updating the PivotTable 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 PivotTable.