Making an auto-refreshing PivotTable in Excel

This topic may be a little advanced, but if you follow this through step by step, even if you don’t understand every formula and the line of VBA code, you will be able to convert any PivotTable into an auto-refreshing table.  For this you will need to know how to make a PivotTable, and how to record a macro.

Pivot tables are fantastic for quickly analysing information from a larger data source, but often we add data to the data source and either forget to increase the Data Range that the PivotTable is referring to, or forget to refresh the table.  This solution addresses both of these problems, meaning that when you switch to the PivotTable tab, you will be looking at the latest information every time.
This technique has two parts; firstly an auto-expanding named range which provides the range for the PivotTable to refer to, and secondly a single line of VBA code to refresh the PivotTable when its worksheet is clicked on.
So let’s begin.  Our data for our PivotTable is in Sheet1 and begins in cell A1, has 6 columns and over 200 rows (and we are assuming that the number of rows will increase as our project progresses):
Inline image 1
To make our auto-expanding named range, click on the Formulas tab, and click Name Manager and click New… and we type in a formula into the “Refers to:” box, which I will explain below.  Note that this entire step can be avoided if your data range is converted into an Excel “Table”, which in one way is easier, but can give you more problems if you’re not familiar with working in “Excel Tables” – something I’m not going to go into detail in here.
Inline image 2
This formula in the “refers to” box is a combination of the OFFSET and COUNTA functions; I won’t go into the detail of how these functions work (we have a blog tutorial on using offset here).  My formula is =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6) but you will need to alter your formula as follows:
Inline image 4
Now we have a Named Range which we have named “PivotRange” that will auto-expand as more data is added to it.  We can now use this named range to create a pivot table.  On the Insert tab, click PivotTable and in the “Table/Range” box enter your named range.  If instead you have converted your range into an “Excel Table” you can enter the name of the table here.
Inline image 3
Now make your pivot table however you like.  Once you’re finished, you need to record a very simple macro.  Just click record, then refresh your PivotTable, then stop recording.
Then, on the Developer tab, select Macros, find the macro you’ve recorded and click “Edit”
Inline image 5
Your code may vary slightly, depending on the name generated for your PivotTable.  You can ignore any superfluous code here, just select and copy the line of code that ends with “PivotCache.Refresh”.
Inline image 6
OK bear with me, we are nearly there!  Once you have copied that line of code you can close the “Microsoft Visual Basic for Applications” screen and go back to your Excel file.  Now what we need to do is make it so Excel runs that line of code each time your PivotTable worksheet is selected.  To do this, right click on the tab with your PivotTable on, and select “View Code”.
Inline image 7
Now change the drop-down where it says “(General)” and change this to “Worksheet”:
Inline image 8
 And in the right drop-down, select “Activate”:
Inline image 9
It will now automatically create a section of code that says “Private Sub Worksheet_Activate()”.  Beneath this, paste in the line of code you copied:
Inline image 11
Now Excel will run that line of code each time that worksheet tab is activated.  You can now close Visual Basic for Applications for the last time.
You’re all done!  Now your PivotTable will always be up to date when you are viewing it.  I would recommend familiarising yourself with setting up PivotTables in this way so you don’t get caught out with PivotTables displaying out-of-date information once your data source has been updated.
If you have found this tutorial useful, please share it with your colleagues and friends.  To stay up to date with our latest tutorial blog updates, click here to subscribe to our mailing list.

Leave a Reply

Your email address will not be published. Required fields are marked *