Tuesday, May 06, 2008

Create Pivot Table using Excel VBA


Macro to Create a Pivot Table from New Pivot Cache


Sub Create_Pivot_Table_From_Cache()

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

oWS = ActiveSheet

oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase, oWS.UsedRange)

oPT = oPC.CreatePivotTable(oWS.[D20], "Pivot From Cache", True)

oPT.AddFields(oPT.PivotFields("Item").Name, oPT.PivotFields("Customer").Name)

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

End Sub

PivotCache represents the collection of memory caches from the PivotTable reports in a workbook. Each memory cache is represented by a PivotCache object. The above example creates a pivotcache from existing data and then using the cache a pivot table is created




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

4 comments:

  1. its not working....it shows a syntax error in line

    oPT.AddFields(oPT.PivotFields("Item").Name, oPT.PivotFields("Customer").Name)
    oPT.AddDataField(oPT.PivotFields("Qty"), "Quantity", xlSum)

    hav you got any solution for that

    ReplyDelete
  2. Anonymous12:26 PM

    I noticed something about that when I was reading. Is it because it simply says AddFields, and not AddColumnField or something like that?

    ReplyDelete
  3. Anonymous5:39 AM

    The SET statements have been omitted before variables are assigned to the objects oWS, oPC and oPT

    ReplyDelete
  4. Anonymous5:35 AM

    The bracketing is wrong. Input the set statements as above and use the below code.

    oPT.AddFields (oPT.PivotFields("Item").Name), oPT.PivotFields("Customer").Name
    oPT.AddDataField (oPT.PivotFields("Quantity")), "Qty", xlSum

    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