运行ChangePivotCache时第二次迭代数据透视表出现Error 5错误

错误原因 #

Error 5（无效的过程调用或参数）在第二次执行pt.ChangePivotCache pc时触发，核心原因是同一个PivotCache对象无法重复绑定给多个数据透视表。当第一个透视表完成缓存绑定后，该缓存对象的状态发生不可逆变化，再次绑定会触发参数无效错误。此外，原代码未指定透视表版本、盲目使用On Error Resume Next掩盖字段问题，也可能加剧异常。

修正方案 #

核心修改思路 #

为每个需要更新的透视表单独创建适配的PivotCache，避免复用同一缓存对象；同时优化字段检查逻辑，避免隐式错误。

修正后的完整代码 #

Sub tabelasDinamicas(wsDados As Worksheet, wbAnualizacao As Workbook)
    Dim pt As PivotTable
    Dim pc As PivotCache
    Dim tabelaExiste As Boolean
    Dim rangeDados As Range
    Dim ultimaCol As Long, ultimaLinha As Long
    Dim ws As Worksheet
    
    ' 获取完整数据源范围
    ultimaCol = wsDados.Cells(1, wsDados.Columns.Count).End(xlToLeft).Column
    ultimaLinha = wsDados.Cells(wsDados.Rows.Count, 1).End(xlUp).Row
    Set rangeDados = wsDados.Range(wsDados.Cells(1, 1), wsDados.Cells(ultimaLinha, ultimaCol))
    
    For Each ws In wbAnualizacao.Worksheets
        If ws.Name <> "Dados" Then
            tabelaExiste = False
            
            ' 为当前工作表单独创建PivotCache，避免复用冲突
            Set pc = wbAnualizacao.PivotCaches.Create( _
                SourceType:=xlDatabase, _
                SourceData:=rangeDados, _
                Version:=xlPivotTableVersion15) ' 指定版本提升兼容性
            
            For Each pt In ws.PivotTables
                tabelaExiste = True
                ' 先释放旧缓存关联
                Set pt.PivotCache = Nothing
                ' 绑定新缓存并刷新
                pt.ChangePivotCache pc
                pt.RefreshTable
            Next pt
            
            ' 无透视表则创建新表
            If Not tabelaExiste Then
                Dim ptNovo As PivotTable
                Dim ptRange As Range
                Set ptRange = ws.Range("B5")
                
                Set ptNovo = pc.CreatePivotTable( _
                    TableDestination:=ptRange, _
                    TableName:=ws.Name)
                
                With ptNovo
                    ' 先检查字段是否存在，避免错误
                    If Not IsError(Application.Match("GRUPOS", wsDados.Rows(1), 0)) Then
                        .PivotFields("GRUPOS").Orientation = xlRowField
                    Else
                        MsgBox "工作表[" & ws.Name & "]的数据源中不存在GRUPOS字段，无法设置行字段"
                    End If
                    ' 可在此添加其他透视表字段配置
                End With
            End If
            
            ' 释放当前缓存对象
            Set pc = Nothing
        End If
    Next ws
End Sub

关键调整点 #

1. 单独创建PivotCache：循环处理每个工作表时，重新创建专属的缓存对象，彻底避免复用导致的绑定冲突。
2. 指定透视表版本：添加Version参数，适配不同Excel版本的透视表格式，减少兼容性问题。
3. 释放旧缓存：在绑定新缓存前执行Set pt.PivotCache = Nothing，确保旧缓存完全解除关联。
4. 显式字段检查：移除On Error Resume Next，改为主动检查字段是否存在，避免掩盖真实错误。
5. 明确变量类型：为wsDados和wbAnualizacao添加类型声明，避免隐式转换引发的异常。

额外排查要点 #

• 确认所有非“Dados”工作表的透视表字段，均存在于rangeDados数据源中。
• 确保执行宏时，目标工作表处于未保护状态，保护模式下无法修改透视表缓存。
• 若仍有异常，可在pt.ChangePivotCache pc前添加ws.Activate，确保工作表处于激活状态（部分Excel版本对非激活工作表的透视表操作有限制）。

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