excel - Add all fields into a Pivot Table as Max

Question

I need to create a pivot table with all fields in the row section as MAX as the summarised value. I found a code that NEARLY does what I want here: https://www.pivot-table.com/2013/08/21/add-all-remaining-fields-to-pivot-table/

The only problem is this code doesn't set what kind of value is showed, so most of them came out as SUM or COUNT. Does anyone know what kind of changes to the code would make sure the values are MAX? Any help would be much appreciated!

Sub AddAllFieldsValues()
Dim pt As PivotTable
Dim iCol As Long
Dim iColEnd As Long

Set pt = ActiveSheet.PivotTables(1)

With pt    
   iCol = 1
   iColEnd = .PivotFields.Count

    For iCol = 1 To iColEnd
        With .PivotFields(iCol)
          If .Orientation = 0 Then
              .Orientation = xlDataField
          End If
        End With
    Next iCol
End With

End Sub

question from:https://stackoverflow.com/questions/65911036/add-all-fields-into-a-pivot-table-as-max

Answer 1

You can change it by using .Function in your case .Function = xlMax you will need to added after orientation. This method allows you to use count, sum, avg and other xl functions

        With .PivotFields(iCol)
          If .Orientation = 0 Then
             .Orientation = xlDataField
             .Function = xlMax
          End If

Now, beware that you are looping through all the pivot fields in your pivot table, therefore if you incorporate .Function = xlMax all will appear as max