Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Data_sht As Worksheet, ps As Worksheet, pn$
If MsgBox("Update pivot table?", vbYesNo) = vbNo Then Exit Sub
Set Data_sht = Sh
Set ps = ThisWorkbook.Worksheets("first")
MsgBox "Active sheet is " & Sh.Name
pn = "PivotTable1"
ps.PivotTables(pn).ChangePivotCache ThisWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Data_sht.Name & "!" & _
Data_sht.[a1].CurrentRegion.Address(ReferenceStyle:=xlR1C1))
ps.PivotTables(pn).RefreshTable
MsgBox pn & "'s data source range has been updated!" & vbLf & _
"Source is " & ps.PivotTables(pn).SourceData
End Sub