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"
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.
Here is instructions on how to insert the event code to your "pivot table data source" worksheet:
Click "Developer" tab on the ribbon.
Click "Visual Basic" button
Double click on "Data" sheet in project explorer window.
Insert event code into worksheet code window to the right of the project explorer window.
Private Sub Worksheet_Change(ByVal Target As Range)
The code is copied into the code window for sheet "Data". The Worksheet_Change sub is executed when a cell is changed in sheet (Data).
This line of code refreshes the pivot table on sheet Pivot table whenever a cell is changed on sheet Data.
Click on any cell in your pivot table and go to tab "Options" to see the name of your pivot table. Make sure to use that name in code above.
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.