Trying to get a macro to loop over PivotFields

[TomCon]

I recorded this macro.

Sub Macro5()
'
' Macro5 Macro
'

'
    Range("A4").Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of SiEv2P.a")
        .Caption = "Average of SiEv2P.a"
        .Function = xlAverage
        .NumberFormat = "0.000"
    End With
End Sub

But, i can't get it to loop over all the fields in my PivotTable, all of which i want to change to "Average" (about 25 fields)
Of course the Caption would have to change as well, i understand that. I have tried various solutions and none of them work. The loops seem to choke over the ".formula" step, with a VBA error.

I have done this in the immediate window, after changing the field where the macro was recorded back to Sum. Note that this PivotField is PivotField(1).
?ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of SiEv2P.a").name
Sum of SiEv2P.a
?ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of SiEv2P.a").Caption
Sum of SiEv2P.a
?ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of SiEv2P.a").Formula
-4157
?ActiveSheet.PivotTables("PivotTable2").PivotFields(1).name
SiEv2P.a
?ActiveSheet.PivotTables("PivotTable2").PivotFields(1).Caption
Sum of SiEv2P.a
?ActiveSheet.PivotTables("PivotTable2").PivotFields(1).Formula
=> VBA Err msgbox pops up, nothing displayed in Immediate window.

Why does PivotFields(1).name give a different result than PivotFields("SiEv2P.a").name?

Why can't I get the Formula property in the immediate window for this PivotField, whether i represent it by its Name or by its field index? [Bad ramifications in trying to write code in a loop]

This is not just a theoretical question, as when i am trying to create a loop, i cannot create one that works.

If you can create a loop that will work for PivotField index 1 to 25, please post. I can figure out how to change the Caption text to something unique in each loop.

Thanks!

 

[Alex Blakenburg]

The formula property only exists for a calculated field.
See if the below gives you any ideas about looping through the Fields.

Sub LoopThroughFields()

    Dim PvtTbl As PivotTable
    Dim PvtFld As PivotField
    Dim i As Long
    
    'Set PvtTbl = ActiveSheet.PivotTables("PivotTable2")
    Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
    
    For i = 1 To PvtTbl.PivotFields.Count
        Set PvtFld = PvtTbl.PivotFields(i)
        Debug.Print PvtFld.Name
    Next i
End Sub

 

[TomCon]

The formula property only exists for a calculated field.
See if the below gives you any ideas about looping through the Fields.

Sub LoopThroughFields()

    Dim PvtTbl As PivotTable
    Dim PvtFld As PivotField
    Dim i As Long
  
    'Set PvtTbl = ActiveSheet.PivotTables("PivotTable2")
    Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
  
    For i = 1 To PvtTbl.PivotFields.Count
        Set PvtFld = PvtTbl.PivotFields(i)
        Debug.Print PvtFld.Name
    Next i
End Sub

Thanks for the suggestion/help!

Yes, indeed the above code does loop through the PivotTable fields. But there is still a problem if you want to change the Function from something like xlSum to xlAverage.

Here is a recorded macro with changing the field's function to xlAverage.
Sub Macro1()
'
' Macro1 Macro
'

'
Range("G1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of F2")
.Caption = "Average of F2"
.Function = xlAverage
.NumberFormat = "0.0"
End With
End Sub

So, i then worked that "With" into your code and get the following
Sub LoopThroughFields2()
Dim PvtTbl As PivotTable
Dim PvtFld As PivotField
Dim i As Long

'Set PvtTbl = ActiveSheet.PivotTables("PivotTable2")
Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")

For i = 2 To PvtTbl.PivotFields.Count
Set PvtFld = PvtTbl.PivotFields(i)
Debug.Print PvtFld.Name

With PvtFld
.Caption = "FX" & i
.Function = xlAverage
.NumberFormat = "0.0"
End With
Next i

End Sub

When i run the revised LoopThroughPivotFields2(), the Debug.Print properly prints the field name, as expected. The .Caption property is changed, as expected. When execution gets to the statement that tries to set the .Function property, a VBA error dialog box pop up. "Run time error 1004. Unable to set the Function property of the PivotField class"

My basic desire is to have a loop over PivotFields, where i can set the Function for the field (xlSum, xlAverage, etc) to something different than it is, and set the Number format.

Commenting out the attempt to set the .Function property, the macro runs without error BUT the .NumberFormat is not changed in the PivotTable for the field. So, that also appears to be a problem.

Anybody have any solution as to how to do this?

 

[Alex Blakenburg]

See if the below does what you need it to do.

Sub LoopThroughDataFieldsChangeAggregation()

    Dim PvtTbl As PivotTable
    Dim PvtFld As PivotField
    Dim i As Long
   
    Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
   
    For i = 1 To PvtTbl.DataFields.Count
        With PvtTbl.DataFields(i)
            .Caption = "FX" & i         ' Modify this to i + 1 if you want it to start numbering at 2
            .Function = xlAverage
            .NumberFormat = "0.0"
        End With
    Next i
End Sub

FYI - Bill Jelen video (< 6 mins)

 

[TomCon]

See if the below does what you need it to do.

Sub LoopThroughDataFieldsChangeAggregation()

    Dim PvtTbl As PivotTable
    Dim PvtFld As PivotField
    Dim i As Long
  
    Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
  
    For i = 1 To PvtTbl.DataFields.Count
        With PvtTbl.DataFields(i)
            .Caption = "FX" & i         ' Modify this to i + 1 if you want it to start numbering at 2
            .Function = xlAverage
            .NumberFormat = "0.0"
        End With
    Next i
End Sub

FYI - Bill Jelen video (< 6 mins)

Yes, indeed it does. Have to use "dataFields" not "PivotFields". Interesting. But very good for me to know as i want to do this operation fairly often. Sorry it took me a while to update the answer as correct! Tom

 

[Alex Blakenburg]

No problem. Thanks for the follow-up and glad I could help.