building multiple pivot tables from one data source

Question

I'm trying to create multiple pivot tables from one data source but keep getting reuse from the first pivot table - this is my code -

int lastRow = ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
pivotTableName = @"PivotPableNameA";
pivotData[0] = ws.Range["A1:B"+lastRow];
pivotDestination[0] = wsP.Range["A1", useDefault];

wb.PivotTableWizard(
        XlPivotTableSourceType.xlDatabase,
        pivotData[0],
        pivotDestination[0],
        pivotTableName,
        true,
        true,
        true,
        true,
        useDefault,
        useDefault,
        false,
        false,
        XlOrder.xlDownThenOver,
        1,
        useDefault,
        useDefault
);

// Set variables used to manipulate the Pivot Table.
pivotTable[0] = (PivotTable)wsP.PivotTables(pivotTableName);

itemcodePivotField[0] = (PivotField)pivotTable[0].PivotFields(2);
descriptionPivotField[0] = (PivotField)pivotTable[0].PivotFields(1);
countPivotField[0] = (PivotField)pivotTable[0].PivotFields(2);

// Format the Pivot Table.
pivotTable[0].Format(XlPivotFormatType.xlReport2);
pivotTable[0].InGridDropZones = false;
pivotTable[0].SmallGrid = false;
pivotTable[0].ShowTableStyleRowStripes = true;
pivotTable[0].TableStyle2 = "PivotStyleLight1";

// Row Fields
itemcodePivotField[0].Orientation = XlPivotFieldOrientation.xlRowField;
itemcodePivotField[0].Position = 1;

// Colum Fields
descriptionPivotField[0].Orientation = XlPivotFieldOrientation.xlColumnField;
descriptionPivotField[0].Position = 1;

// Data Field
countPivotField[0].Orientation = XlPivotFieldOrientation.xlDataField;
countPivotField[0].Function = XlConsolidationFunction.xlCount;

I tried taking twice the data source but it keeps re-using the first table and just moving it to another cell Y.D

Answer 1

I found the solution. you should use PivotCache - to make it clear lets make an array of pivotcache and pivot table - for first table in the spreadsheet we would use PivotCache[0] and PivotTable[0] and for second pivot table we should use PivotCache[1] and PivotTable[1] as shown in the example -

        pivotCache[0] = (Excel.PivotCache)wb.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, pivotData[0]);
        pivotTable[0] = (Excel.PivotTable)wsP.PivotTables().Add(PivotCache: pivotCache[0], TableDestination: pivotDestination[0], TableName: pivotTableName);


        pivotCache[1] = (Excel.PivotCache)wb.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase, pivotData[1]);
        pivotTable[1] = (Excel.PivotTable)wsP.PivotTables().Add(PivotCache: pivotCache[1], TableDestination: pivotDestination[1], TableName: pivotTableName);

this part shouldn't be used for my point of view

wb.PivotTableWizard(....)
// Set variables used to manipulate the Pivot Table.
pivotTable[0] = (PivotTable)wsP.PivotTables(pivotTableName);

wb.PivotTableWizard(....)
// Set variables used to manipulate the Pivot Table.
pivotTable[1] = (PivotTable)wsP.PivotTables(pivotTableName);