Code to sort pivot table by 'Grand Total'

[muaythai]

Version: Excel 03 SP1.

I'm using the following code to try and automate a pivot table refresh by descending on the grand total.

The data contained in the table is just a count of occurrences but whenever I use this it comes up with :-

'Can't identify which pivot field to sort by'. Error

Any assistance would be much appreciated.

Sub SortGrandTotal()
Columns("B:IV").Select
Selection.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Activate
MyCell1 = ActiveCell.Address
MyCol = ActiveCell.Column
MyCell2 = Cells(92, MyCol).Address
Range(MyCell1, MyCell2).Select
Selection.Sort Key1:="b5", Order1:=xlDescending, Type:=xlSortValues, OrderCustom:=1, Orientation:=xlTopToBottom

End Sub


MT

 

[BrianB]

Cannot see what you are trying to do.

If you want the total column sorted, doubleclick the grey header, then Advanced, and the option is there.