How to stop VBA changing the sort order

[Christof]

Hi, hopefully a quick one..
Is there something I can add my VBA to stop the pivot table that it is in from resorting

        pt4.DataFields(1).Orientation = xlHidden
        pt4.PivotFields(sumField).Orientation = xlDataField

pt4 is the PivotTable shown
"sumfield" is either "Pax" or "Revenue" as I want to be able to select between the two, but when it changes it resorts it.
Basically, whatever it's a "Sum of.." needs to be sorted Largest to Smallest on the Grand Total.

 

[Chihiro]

Just add sorting operation to your code.

Each time you set different field orientation, sort is reset. As Pivot sorting is dependent on context.

To get general syntax of sorting on values. Use macro recorder and apply your sort operation manually.