When you update a pivot table’s source data in Excel, the pivot table does not update automatically.
You can set the pivot table to update when the Excel file opens, but that doesn’t help if you’re making changes after the file opens.
To define name, highlight the range you want and then overwrite the name in the "Name box" (above column A on your sheet).
To do this: Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option.
One way to do this might be as follows: In a regular module enter the following: If you really want them to refresh as soon as the data changes (although I don't think it's a good idea), you can apply the same procedure, but using the Worksheet_Change event in the Sheet1 (source data) worksheet module, something like this: If you do actually use this, you should add further restrictions as required depending on the setup of your source data so only changes in that particular region of the worksheet activate the refresh procedure.
For more on the distinction between regular and worksheet modules see: Excel VBA -- Adding Code to Excel Workbook For other Pivot Table refresh options see: Refresh Pivot Table via VBA.
Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update.
You need to force a refresh every time there are changes. You can also by selecting any cell in the Pivot Table and using the keyboard shortcut ALT F5.