Invalid Procedure Call or Argument when Creating a Pivot Table

Question

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

The whole code is as below:

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

score 7 · Accepted Answer · answered Jun 10 '13 at 10:24

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

score 1 · Answer 2 · answered Jul 11 '18 at 19:24

For me, this error occurred after upgrading to a newer version of Excel because it was not recognizing the DefaultVersion parameter xlPivotTableVersion15 enumeration when creating the pivot table using the CreatePivotTable function.

This is what did not work for me, it had nothing to do with spaces or destination name:

Workbooks(WorkbookName).PivotCaches(1).CreatePivotTable _
    TableDestination:=DestinationString, TableName:=TableNameString, _ 
    DefaultVersion:=xlPivotTableVersion15

And this is the working version:

Workbooks(WorkbookName).PivotCaches(1).CreatePivotTable _
    TableDestination:=DestinationString, TableName:=TableNameString, _ 
    DefaultVersion:=6

Invalid Procedure Call or Argument when Creating a Pivot Table

2 Answers2

Linked