Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summing a Pivot Table in an Excel macro (2003)

    I have a macro that creates a pivot table, and it is summing all the fields. I just want it to sum by the first name field only. Does anyone know how to do this? My code is below.

    With ActiveSheet.PivotTables("PT1")
    .AddFields RowFields:=Array("AFFILIATION", "REPSSN", "FIRSTNAME", "LASTNAME", "AGENCYCODE", "SUBAGENCYCODE", "PROD_COMM_GROUP_CODE"), _
    ColumnFields:=Array("POST_MONTH_NO", "POST_MONTH")
    .PivotFields("GDC").Orientation = xlDataField
    End With

    Thanks,
    Chris

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Summing a Pivot Table in an Excel macro (2003)

    I think you would need to loop through each of the row fields you don't want summed and set all the subtotals to False - e.g.:
    <pre>ActiveSheet.PivotTables("PT1").PivotFields("A FFILIATION").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PT1").PivotFields("REPSSN ").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)
    </pre>

    etc.

    Edit:
    As a slightly shorter alternative you can use:
    <pre>ActiveSheet.PivotTables("PT1").PivotFields("A FFILIATION").Subtotals(1) = True
    ActiveSheet.PivotTables("PT1").PivotFields("AFFILI ATION").Subtotals(1) = False
    </pre>


    since setting subtotals(1) - i.e. Automatic - sets all others False anyway.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summing a Pivot Table in an Excel macro (2003)

    Use an instruction such as the following for each field you do NOT want to sum:

    .PivotFields("AFFILIATION").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)

  4. #4
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a Pivot Table in an Excel macro (2003)

    Rory,

    It works great. Thanks a lot for your help.

    Thanks,
    Chris

  5. #5
    Lounger
    Join Date
    Feb 2006
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summing a Pivot Table in an Excel macro (2003)

    Hans,

    It works great. I really appreciate your help.

    Thanks,
    Chris

Posting Permissions

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