Change all values in a Pivot Table from "Count" to "Sum" 1

[MattGreer]

I've got a pivot table in an Excel 2010 spreadsheet that gets its data from Access. Whenever I add fields to the "Values" area, it always defaults to "Count" instead of "Sum". I would appreciate it if someone could tell me how to craft a "For...Each" loop to run through all the items in the Values area and change them to Sum, rather than Count.

I recorded a macro of me changing one manually and this is what I got:

    With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Count of Oil Volume VolTDay")
        .Caption = "Sum of Oil Volume VolTDay"
        .Function = xlSum
End With

I tried typing in a For/Each loop manually with no success.

Additionally, I'll have to extract the name of each item in the Values area so I can change it to reflect the change to xlSum.

I appreciate your help!


Thanks!!


Matt

 

[PHV]

Typed, untested:

For Each PT In ActiveSheet.PivotTables
  For Each PF In PT.PivotFields
    If PF.Function = xlCount Then
      PF.Caption = Replace(PF.Caption, "Count", "Sum")
      PF.Function = xlSum
    End If
  Next
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MattGreer]

I get the error "Unable to get the Function property of the PivotField class", and the third line of the code is highlighted. I tried several variations on that theme but was unable to get the Function state/value out of it. I got it to debug.print PF.Name; that prints out all the fields in the PivotTable Field List in the Fields Selection box.

Thanks!!


Matt

 

[MattGreer]

One of the issues here might be that this is coming from a crosstab query. The column is "Values", and the "Date" is dates. Those are the only fields that are not hidden even though I have six or seven things other than "Date" selected in the Field List.

Thanks!!


Matt

 

[MattGreer]

PHV + Google = success

Figured it out. Here's the working code. What's really interesting is that the names are changed automatically from "Count of" to "Sum of".

I love this stuff.

Private Sub PivotPractice2()
Dim PT As PivotTable
Dim PF As PivotField


For Each PT In ActiveSheet.PivotTables
  For Each PF In PT.VisibleFields
        Debug.Print PF.Name
    If PF.Name <> "Date" And PF.Name <> "Values" Then
        PF.Function = xlSum
    End If
  Next
Next
End Sub

Thanks!!


Matt