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

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