Tuesday, May 06, 2008

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

Macro to Create a Pivot Table from Existing Pivot Cache

Many times you will have a Pivot Table created from a pivot cache and you have a requirment to create another pivot table from the same data. In such cases, instead of creating a new cache, you can use the existing pivot cache to create another pivot table. This will save a good amount of memory too.

Sub Create_Pivot_Table_From_Existing_Cache()

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

oWS = ActiveSheet

If oWS.PivotTables.Count <>Then Exit Sub

oPC = oWS.PivotTables(1).PivotCache

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

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

oPT.AddDataField(oPT.PivotFields("Customer"), "Quantity", xlCount)

End Sub

Here we are checking if any Pivot Table exist in that particular sheet; if it exists we are using the same cache of the pivot table to create another pivot table












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. Anonymous5:35 AM

    how can i create a pivot table based on two existing pivot tables.. its like putting both table datas together?

    ReplyDelete
  2. Anonymous3:37 PM

    I can't run this code...is it incomplete?

    ReplyDelete
  3. Anonymous9:14 PM

    how can create a macro in pivot table any were.

    ReplyDelete
  4. Jerry Sullivan10:09 PM

    The code is missing the Set keyword in two places...

    Set oPC = oWS.PivotTables(1).PivotCache
    Set oPT = oPC.CreatePivotTable(oWS.[J1], "Pivot From Existing Cache", True)

    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