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
Hello,
ReplyDeleteWhen trying this macro, why do I get an error: "synthax error". I copy/pasted exactly what you wrote.
Thank you