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
how can i create a pivot table based on two existing pivot tables.. its like putting both table datas together?
ReplyDeleteI can't run this code...is it incomplete?
ReplyDeletehow can create a macro in pivot table any were.
ReplyDeleteThe code is missing the Set keyword in two places...
ReplyDeleteSet oPC = oWS.PivotTables(1).PivotCache
Set oPT = oPC.CreatePivotTable(oWS.[J1], "Pivot From Existing Cache", True)