Private Sub Workbook_Open()
    Dim ws As Worksheet
    Dim pt As PivotTable
    
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
End Sub

Sub AutoRefreshPivotTables()
    Dim pt As PivotTable
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
    
    ' 设置10分钟后再次刷新
    Application.OnTime Now + TimeValue("00:10:00"), "AutoRefreshPivotTables"
End Sub

Sub UpdatePivotSource()
    Dim wsData As Worksheet
    Dim wsPivot As Worksheet
    Dim pt As PivotTable
    Dim rngSource As Range
    Dim strSource As String
    
    Set wsData = Worksheets("数据工作表") '修改为你的数据工作表名
    Set wsPivot = Worksheets("透视表工作表") '修改为你的透视表工作表名
    Set pt = wsPivot.PivotTables(1) '修改为你的透视表索引或名称
    
    '动态获取数据范围
    Set rngSource = wsData.Range("A1").CurrentRegion
    
    '更新透视表数据源
    strSource = wsData.Name & "!" & rngSource.Address(ReferenceStyle:=xlR1C1)
    pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=strSource)
    
    pt.RefreshTable
End Sub