The issue here is that a pivot table doesn't know if
source data has changed
source cell reference has changed
That is why you have to manually change the source cell reference and refresh pivot table, this is very easy to forget. However, this article solves your problems using an excel defined table and three lines of event code.
In this example there are two worksheets, "Data" and "Pivot table", to make it simple. The following image shows you some of the data on worksheet "Data"
Watch a video
Create a dynamic source range
Excel is not smart enough to know when you have added values to your data, the source cell reference won't adjust automatically. However Excel defined tables (Excel 2007 and later versions) has that feature and luckily it is easy to implement.
You will also need event code to refresh your pivot table but first let's build an Excel defined table.
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Your pivot table is now instantly refreshed whenever a cell value on worksheet "Data" has changed, and the pivot table source data range is adjusted if values have been added/deleted to your excel defined table.