向现有工作表添加多个数据透视表时触发运行时错误'5'

我帮你排查了这个问题，运行时错误'5'（无效的过程调用或参数）大概率是因为目标单元格被现有内容占用，或者代码里依赖Select操作导致的上下文冲突。结合你提供的代码，问题主要出在这几点：

1. 目标位置冲突：你指定的TableDestination:="Pivot Tables!R3C6"可能已经被第一个透视表的范围覆盖了，Excel不允许在已被其他透视表或数据占用的区域创建新透视表。
2. 冗余的Select操作：依赖Select切换工作表和单元格很容易引发上下文错误，而且不是VBA的最佳实践。
3. 重复改名操作：先创建名为PivotTable6的透视表再改名为Analyst，多了一步没必要的操作，也可能引发命名冲突。

修正后的代码（直接在现有工作表添加第二个透视表） #

Sub AddSecondPivotToExistingSheet()
    Dim sourcePivot As PivotTable
    Dim newPivot As PivotTable
    Dim targetSheet As Worksheet
    Dim targetRange As Range
    
    ' 引用源透视表和目标工作表（避免Select）
    Set sourcePivot = ThisWorkbook.Worksheets("Pivot Tables").PivotTables("Calls by Region/Analyst")
    Set targetSheet = ThisWorkbook.Worksheets("Pivot Tables")
    
    ' 确认目标位置为空白单元格（这里用F3，需确保和第一个透视表不重叠）
    Set targetRange = targetSheet.Range("F3")
    
    ' 用源透视表的缓存直接创建新透视表，同时指定最终名称
    Set newPivot = sourcePivot.PivotCache.CreatePivotTable( _
        TableDestination:=targetRange, _
        TableName:="Analyst", _
        DefaultVersion:=xlPivotTableVersion12)
    
    ' 配置新透视表的字段和格式
    With newPivot
        ' 添加数据字段并修改显示名称
        .AddDataField .PivotFields("Assigned User Name"), "Number of Calls", xlCount
        ' 设置行字段
        With .PivotFields("Assigned User Name")
            .Orientation = xlRowField
            .Position = 1
        End With
        ' 设置行标题名称
        .CompactLayoutRowHeader = "Analyst"
    End With
    
    ' 可选：定位到透视表右侧的单元格
    targetRange.Offset(0, 4).Select
End Sub

关键修正说明 #

• 移除所有Select操作：直接通过对象引用（Set语句）操作工作表和透视表，避免上下文切换错误。
• 确保目标位置空白：打开你的Pivot Tables工作表，检查第一个透视表的边界，确保F3不在其范围内。如果重叠，把targetRange改成其他空白单元格（比如H3）。
• 一步创建并命名：在CreatePivotTable时直接指定TableName:="Analyst"，省去后续改名的步骤，减少出错概率。
• 用With语句简化逻辑：让透视表的配置代码更清晰，减少冗余。

额外注意事项 #

• 检查第一个透视表的范围：如果它的扩展区域覆盖了F3，必须调整目标单元格位置，否则Excel会拒绝创建新透视表。
• 确保数据源稳定：确认Table1的Assigned User Name列存在且名称正确，避免字段引用错误。
• 可添加错误处理：如果想更健壮，可以加入错误捕获逻辑，比如检测目标位置是否被占用：

  On Error Resume Next
  If Not targetRange.Value Is Nothing Then
      MsgBox "目标位置已被占用，请选择空白单元格！"
      Exit Sub
  End If
  On Error GoTo 0
  

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