Excel VBA - Pivot Table Auto referesh

Assuming Your sheet name source data
1. Press ctrl+F3. Name Manager window will appear
2. Click on New
3. Define Name
4. in Refers to text box Enter below formula
=OFFSET('Source Data'!$A$1,0,0,COUNTA('Source Data'!$A:$A),6)
5. Click on ok
6.Now Press Alt + N + V + T short cut keys (or go to insert tab & select Pivot table)
7.Create Pivot table window will appear
8. in Table/Range box enter the name you define in 3 step & click on ok
9. Create Pivot table
10. Right click on pivot table sheet tab
11. click on View code
12. copy & paste below code in vb window ; change pivottable name

Private Sub Worksheet_Activate()
     'If this worksheet is activated, refresh the pivot table
     'Change "Pivot" to your sheet's name
     'Change "PivotTable1" to your pivot table's name

Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
   
End Sub

13. Now whenever you change base data & select pivot table sheet it will get automatically refresh