See Chapter 8, page 146: Automatically Updating a Range Name Reference.
Step 2: Add a VBA Event to automatically refresh the PivotTable report
1. Press Alt+F11, and then double-click the sheet name in the VBAProject pane.
2. From the left dropdown list above the Module sheet, select Worksheet, and from the right dropdown list, select Activate.
3. Copy the code below and paste it into the Module sheet.


ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

4. Press Ctrl+S to save the workbook, and then press Alt+F4 to close the VBA. The PivotTable report is refreshed automatically upon selecting the sheet that contains it.