创建数据透视表时无效的过程调用或参数 [英] Invalid Procedure Call or Argument when Creating a Pivot Table

问题描述

尝试根据一组数据创建数据透视表.通常它第一次运行良好，但是如果您尝试第二次运行，则会抛出无效的过程调用或参数"，并且当您单击debug时，代码将突出显示以下问题.更改数据透视表的名称无济于事.工作表2存在并且填充有数据.数据摘要表也存在并且完全为空

Trying to create a Pivot Table out of a set of Data. Usually it runs fine the first time, but if you try and run it a second time it throws an "Invalid Procedure Call or Argument" and when you click debug the code highlights the below as the issue. Changing the Name of the Pivot Table doesn't help. Sheet 2 exists and is populated with data. The Data-Summary sheet also exists and is completely empty

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet2!R1C1:R1064C4", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Data-Summary!R5C1", TableName:="PivotTable4", _
    DefaultVersion:=xlPivotTableVersion

整个代码如下:

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:D").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet2!R1C1:R1064C4", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Data-Summary!R5C1", TableName:="PivotTable15", _
        DefaultVersion:=xlPivotTableVersion14
    Sheets("Data-Summary").Select
    Cells(5, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Site")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Channel")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Cost"), "Count of Cost", xlCount
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Cost"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Revenue"), "Count of Revenue", xlCount
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("Count of Revenue")
        .Caption = "Sum of Revenue"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

推荐答案

更改表格目标以在工作表名称周围添加引号

change the tabledestination to add quotes round the sheet name

TableDestination:="'Data-Summary'!R5C1"

还有一些代码是不必要的

also some of the code is unnecessary

Sub Macro1()
'
' Macro1 Macro
'

'
   Dim PT                     As Excel.PivotTable
   Set PT = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
                                              "Sheet2!R1C1:R1064C4", Version:=xlPivotTableVersion14).CreatePivotTable( _
                                              TableDestination:="'Data-Summary'!R5C1", TableName:="PivotTable15", _
                                              DefaultVersion:=xlPivotTableVersion14)
   With PT
      With .PivotFields("Site")
         .Orientation = xlRowField
         .Position = 1
      End With
      With .PivotFields("Channel")
         .Orientation = xlColumnField
         .Position = 1
      End With
      .AddDataField .PivotFields("Revenue"), "Sum of Revenue", xlSum
   End With
End Sub

这篇关于创建数据透视表时无效的过程调用或参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！