CODEWITHSUNDEEP
excelvba
jerH
jerH

Reputation: 1299

Error creating PivotTable via VBA

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

Upvotes: 2

Views: 6573

Answers (1)

Axel Richter
Axel Richter

Reputation: 61862

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

Upvotes: 2

Related Questions