Excel VBA: Creating Pivot Table in Specific Destination without Error?

Question

I've been extensively looking into creating a pivot table through vba, and am facing some complications I can't find a solution to. I am trying to create a pivot table in column 4 of the sheet "Pivot". When I try to run my code I get:

"Run time Error 1004: PivotTableWizard method of Worksheet class failed."

Can anyone help? I am still very new to vba. Here is my code, I keep getting error on the second line:

 Sub PivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheets("Information").UsedRange).CreatePivotTable TableDestination:="Pivot!R1C4", TableName:="PivotTable", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)
With ActiveSheet.PivotTables("PivotTable").PivotFields("PN")
  .Orientation = xlRowField
 .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable").PivotFields("Commit")
 .Orientation = xlColumnField
 .Position = 1
End With
ActiveSheet.PivotTables("PivotTable").AddDataField ActiveSheet.PivotTables("PivotTable").PivotFields("Qty"), "Sum", xlSum
End Sub

What's the point of that line when you already specified where to create it in the first line? — Rory
As @Rory mentioned... It looks like you already create the PivotTable titled PivotTable. Comment out the second line, and step through. See what happens when you hit the third line. As long as your initial line creates the PT with the data you want, it should proceed. I am guessing you acquired this by using Record Macro, which is a great start. Try recording the Macro fresh without using the Wizard. It may help you understand how to create PT's strictly with VBA. — Busse

Shai Rado Shai Rado · Accepted Answer · 2017-07-17T16:54:39

You are looking for something like the code below (explanation inside the code comments) :

Option Explicit

Sub AutoPivotTable()

Dim Sht As Worksheet
Dim PvtSht As Worksheet
Dim SrcData As Range
Dim PvtCache As PivotCache
Dim PvtTbl As PivotTable

'-- Determine the data range you want to pivot --
' Set the Worksheet object
Set Sht = ThisWorkbook.Worksheets("Information")
Set SrcData = Sht.UsedRange '1:Z10000").Address(False, False, xlR1C1, xlExternal)

' Set the Pivot Cache from Source Data
Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData.Address(False, False, xlA1, xlExternal))

' Set the Worksheet object where the Pivot Table will be loated
Set PvtSht = ThisWorkbook.Worksheets("Pivot")

On Error Resume Next
'Set the pivot table object
Set PvtTbl = PvtSht.PivotTables("PivotTable") ' check if "PivotTable" Pivot Table already created (in past runs of this Macro)
On Error GoTo 0
If PvtTbl Is Nothing Then '<-- Pivot Table not created >> create it
    ' create a new Pivot Table in "Pivot" sheet
    Set PvtTbl = PvtSht.PivotTables.Add(PivotCache:=PvtCache, TableDestination:=PvtSht.Range("D1"), TableName:="PivotTable")
    With PvtTbl
        With .PivotFields("PN")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("Commit")
            .Orientation = xlColumnField
            .Position = 1
        End With
        .AddDataField .PivotFields("Qty"), "Sum of Qty", xlSum
    End With

Else ' just refresh the Pivot cache with the updated Range
    PvtTbl.ChangePivotCache PvtCache
    PvtTbl.RefreshTable
End If

End Sub

Excel VBA: Creating Pivot Table in Specific Destination without Error?

1 Answers