Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I have a pivot table with several rows need to to be set to none. Is there a better way to do this rather than click each field one by one and set to autosum = none.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Do you mean this?

    [attachment=87173:x.png]

    If not, what exactly do you want to accomplish?
    Attached Images Attached Images
    • File Type: png x.png (8.0 KB, 0 views)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This code does the trick:

    Code:
    Sub NoSubtotals()
    '    turns off subtotals in pivot table
        Dim pt As PivotTable
        Dim pf As PivotField
        On Error Resume Next
        For Each pt In ActiveSheet.PivotTables
            For Each pf In pt.PivotFields
                'First, sets index 1 (Automatic) to True,
                'so all other values are set to False
                pf.Subtotals(1) = True
                pf.Subtotals(1) = False
            Next pf
        Next pt
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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