locked
Creating Pivot table without subtotals and grand totals RRS feed

  • Question

  • Hi,

    I have created Pivot table with two column fields(one outer and anther inner column field). But i am subtotal values as for outer column field.

    Is there any way to avoid these subtotals in creating Pivot table thru vba?

     

    Wednesday, January 18, 2012 8:23 AM

Answers

  • You can try this, running this on the Sheet where the PivotTable is placed, change the PivotTable Name accordingly:

     

    Sub test()
    
    Dim PivTbl As PivotTable
    Dim PivFld As PivotField
    
    Set PivTbl = ActiveSheet.PivotTables("PivotTable8")
      
    With PivTbl
       
       ' loop through all fields in PivotTable, set subtotals to false
       For Each PivFld In .PivotFields
      
          PivFld.Subtotals(1) = False
            
       Next PivFld
      
       ' set grand total to False
         .ColumnGrand = False
         .RowGrand = False
    
    End With
    
    End Sub
    

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Tuesday, January 24, 2012 7:00 AM
    • Marked as answer by danishani Friday, January 27, 2012 9:14 PM
    Wednesday, January 18, 2012 4:53 PM

All replies

  • You can try this, running this on the Sheet where the PivotTable is placed, change the PivotTable Name accordingly:

     

    Sub test()
    
    Dim PivTbl As PivotTable
    Dim PivFld As PivotField
    
    Set PivTbl = ActiveSheet.PivotTables("PivotTable8")
      
    With PivTbl
       
       ' loop through all fields in PivotTable, set subtotals to false
       For Each PivFld In .PivotFields
      
          PivFld.Subtotals(1) = False
            
       Next PivFld
      
       ' set grand total to False
         .ColumnGrand = False
         .RowGrand = False
    
    End With
    
    End Sub
    

    Hope this helps,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Proposed as answer by danishani Tuesday, January 24, 2012 7:00 AM
    • Marked as answer by danishani Friday, January 27, 2012 9:14 PM
    Wednesday, January 18, 2012 4:53 PM
  • if you want to apply for overall Pivot Table Then use below code

        .ColumnGrand = False
        .RowGrand = False

    and if you want to apply to specific field then use below code

    With .PivotFields("Filed Name")
            .Orientation = xlRowField
            .Position = 1
    .Subtotals(1) = False
    End With

    I know This reply is very late but let me know if there is any other way to do it.

           
    Friday, September 1, 2017 12:12 PM