Tuesday, May 06, 2008

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

1 comment:

  1. Anonymous7:41 AM

    Hello,

    When trying this macro, why do I get an error: "synthax error". I copy/pasted exactly what you wrote.

    Thank you

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group