同工作表中PivotTable刷新报错，求数据源与透视表同表的解决方案

问题原因 #

在Worksheet_Change事件触发时，工作表处于编辑状态，直接调用PivotCache.Refresh会和当前工作表的操作产生冲突，尤其是数据源与透视表同属一个工作表时，这种冲突会触发缓存创建失败的报错。

修正后的代码 #

替换你当前的Worksheet_Change事件代码为以下内容：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim originalEvents As Boolean
    
    ' 保存当前事件状态，关闭事件触发避免递归冲突
    originalEvents = Application.EnableEvents
    Application.EnableEvents = False
    
    On Error GoTo Cleanup ' 确保报错后事件能正常恢复
    
    Set ws = Me ' 当前触发事件的工作表
    ' 刷新当前工作表内的所有透视表
    For Each pt In ws.PivotTables
        pt.PivotCache.Refresh
        pt.RefreshTable
    Next pt
    
    ' 若需刷新工作簿内其他工作表的透视表，可取消注释以下代码
    ' Dim wb As Workbook
    ' Set wb = ActiveWorkbook
    ' For Each ws In wb.Sheets
    '     If ws.Name <> Me.Name Then
    '         For Each pt In ws.PivotTables
    '             pt.PivotCache.Refresh
    '         Next pt
    '     End If
    ' Next ws

Cleanup:
    ' 恢复事件触发状态
    Application.EnableEvents = originalEvents
    If Err.Number <> 0 Then
        MsgBox "刷新透视表时出错: " & Err.Description, vbExclamation
    End If
End Sub

关键优化点 #

• 关闭事件触发：操作前设置Application.EnableEvents = False，避免透视表刷新导致的单元格变化再次触发Worksheet_Change事件，造成递归循环和冲突。
• 错误处理：通过On Error GoTo Cleanup确保无论是否报错，事件触发状态都会恢复，防止后续Excel操作的事件失效。
• 针对性刷新：直接遍历当前工作表的透视表，减少不必要的遍历操作。

额外注意事项 #

• 如果工作表有保护，需在刷新前添加ws.Unprotect Password:="你的密码"，刷新后执行ws.Protect Password:="你的密码"恢复保护。
• 若数据源范围较大，可添加Application.ScreenUpdating = False和Application.Calculation = xlCalculationManual提升刷新速度，操作后恢复为True和xlCalculationAutomatic。

内容的提问来源于stack exchange，提问作者Umut K