Create Pivot Table from VBA using Wizard

Use PivotTable Wizard Programmatically using Excel VBA

You can use PivotTableWizard method of Worksheet object to create a new PivotTable report. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache

Sub Create_Pivot_Table_Using_Wizard()

Dim oPT As PivotTable

Dim oWS As Worksheet

On Error GoTo Err_PT

If ActiveSheet.Type = xlWorksheet Then

oWS = ActiveSheet

oPT = oWS.PivotTableWizard(xlDatabase, oWS.UsedRange, oWS.Range("A20"), "PivotFromWizard")

oPT.AddFields("Item", , "Qty")

oPT.AddDataField(oPT.PivotFields("Qty"), "Quanity", xlSum)

oPT.TableRange1.Select()

End If

Exit Sub

Err_PT:

MsgBox(Err.Description)

Err.Clear()

End Sub

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard