Modify Existing Pivot Table RRS feed

  • Question

  • Hello Everyone, 

    I have a speadsheet which already consists of a pivot table with some fields. 

    This pivot table was created manually and I do not have the raw data. The pivot table is all I have. 

    All I want to do is change the pivot table. 

    For example: 

    If my existing pivot table shows fields A, B , C in the Field  list and i want to add Fields D, F and Remove Field A. 

    I am trying to write the code but I get an error in the very first step itself as follows: 

     xlWorksheet = xlWorkbook.Worksheets[1];
                xlWorksheet.Name = "Data Sheet";
                Excel.Worksheet pivotSheet = xlWorkbook.Worksheets[2];
                pivotSheet.Name = "Pivot Sheet";
                Excel.PivotTable pt = (Excel.PivotTable)pivotSheet.PivotTables("Pivot Table");
                pt.SourceData = "Sheet1!R1C1:R5491C42";

    I get an COM error at 

      Excel.PivotTable pt = (Excel.PivotTable)pivotSheet.PivotTables("Pivot Table");

    How do I select Range? Will it be for the entire used range? 

    I am sorry if this is a very basic question.I hope someone can help me. 

    Thank You. 

    Wednesday, October 28, 2015 2:43 PM


  • Try recording a macro that does what you want to do, then modify the code. For example, I created a simple pivot table that had columns named aa and bb in the 'rows' part of the pivot table. I removed aa and added dd and got this:

    Sub dummy()
    ' dummy Macro

        ActiveSheet.PivotTables("PivotTable2").PivotFields("aa").Orientation = xlHidden
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("dd")
            .Orientation = xlRowField
            .Position = 2
        End With
    End Sub

    Wednesday, October 28, 2015 4:15 PM