Error creating PivotTable via VBA

Question

Using the macro recorder, I generated the following code to add a PivotTable:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Step 1!R1C1:R30750C34", Version:=6).CreatePivotTable TableDestination:= _
        "Pivot Table 2!R1C1", TableName:="PivotTable3", DefaultVersion:=6
    Sheets("Pivot Table 2").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("STOCK NUMBER")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("DUO QTY"), "Sum of DUO QTY", xlSum
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Excess"), "Sum of Excess", xlSum
    ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Cann"), "Sum of Cann", xlSum

I then made one change, so that the row number for the sourceData would be dynamic rather than fixed:

Sheets("Step 1").Activate
Step1Rows = Sheets("Step 1").UsedRange.Rows.Count

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Step 1!R1C1:R" & Step1Rows & "C34", Version:=6).CreatePivotTable TableDestination:= _
    "Pivot Table 2!R1C1", TableName:="PivotTable3", DefaultVersion:=6

When I run this code, I get a run-time error 5, invalid procedure call or argument. Upon further investigation, I get the same error if I try to run the recorded code. I initially thought it had to do with the hard-coded tableName, but I tried changing that and I still get the same run-time error. Kinda at a loss as to how to proceed right now, so if anyone can spot something wrong or offer suggestions it would be most appreciated

what version of Excel are you using ? not familiar with DefaultVersion:=6 , see MSDN documentation: msdn.microsoft.com/en-us/library/… — Shai Rado
– Shai Rado, Commented Jul 24, 2016 at 4:52
Tried changing it to DefaultVersion:=xlPivotTableVersion15 and got the same error. On a lark tried DefaultVersion:=xlPivotTableVersion16 and got a variable undefined error.... — jerH
– jerH, Commented Jul 24, 2016 at 4:59
i prefer setting variables for sheets and ranges, it's safer later not to get errors (at least eliminate this factor when troubleshooting). There is a great example at: globaliconnect.com/excel/… — Shai Rado
– Shai Rado, Commented Jul 24, 2016 at 5:12

Axel Richter · Accepted Answer · 2016-07-24 06:16:30Z

2

Multiple issues here.

First: The macro recorder does something wrong with the sheet name in TableDestination:= "Pivot Table 2!R1C1". Since it has spaces within the name it must be TableDestination:= "'Pivot Table 2'!R1C1". Note the name within single quotes.

Second: You cannot create a new pivot table in a cell range which already contains a pivot table. So either you must delete the old pivot table first or you must update the already present pivot table.

If the sheet "Pivot Table 2" only contains this pivot table and nothing else, then the simplest thing would be:

Step1Rows = ActiveWorkbook.Worksheets("Step 1").UsedRange.Rows.Count

ActiveWorkbook.Worksheets("Pivot Table 2").Cells.Delete

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Step 1!R1C1:R" & Step1Rows & "C34", Version:=6).CreatePivotTable TableDestination:= _
        "'Pivot Table 2'!R3C1", TableName:="PivotTable3", DefaultVersion:=6

edited Jul 24, 2016 at 6:16

answered Jul 24, 2016 at 6:06

Axel Richter

62.5k7 gold badges70 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jerH Over a year ago

The single quotes were the issue...can't believe I didn't see that. The macro creates the sheet "Pivot Table 2" and then adds the pivot table, so there's no issue with that...but good to know. Thanks!

Collectives™ on Stack Overflow

Error creating PivotTable via VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related