Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete detail subtotals in pivot tables (Excel 97)

    I have the following problem.
    I am using VBA to create a pivot table. There are several data fileds selected, with several row items. I can disable creating row grand totals by .RowGrand = False, and the same for the column grand totals.
    However, Excel creates subtotals for each data item which I can not disable from the VBA code. Is there any method or property that I can use here?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Delete detail subtotals in pivot tables (Excel 97)

    You can use something like :<pre> ActiveSheet.PivotTables("PivotTableNAme").PivotFie lds("FieldName").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False).</pre>

    The array elements respresent each type of subtotal function (Sum, Count etc), except for th efirst element which sets Automatic On (True) or Off (False)

    The above line should switch off subtotals by field for the given Field and in the given PT.

    Andrew C

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete detail subtotals in pivot tables (Excel 97)

    Thanks for your help.

    I tried out what you suggested, and I got the error message: Unable to set the Subtotals property of the PivotField class

    Here is the relevant part of the code:

    with <actual pivottable>
    <...>
    With .PivotFields("RSE")
    <...>
    .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
    'in debug mode, it fails at that line with the above message
    End With
    <...>
    end with

    Regards,
    K.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •