Create Additional Pivot Table using Excel VBA (from Existing PivotCache)
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
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
