Control the Sort Order Manually

If your company has been reporting regions in the sequence of South, North, West forever, it is an uphill battle getting managers to accept seeing the report ordered North, South, West just because this is the default alphabetical order offered by pivot tables.

Strangely enough, Microsoft offers a bizarre method for handling a custom sort order in a pivot table. It's called a manual sort order. To change the sort order in the user interface, you simply go to a cell in the pivot table that contains "North," type the word "South," and press Enter. As if by magic, North and South switch places. Of course, all the numbers for North move to the appropriate column.

The VBA code to do a manual sort involves setting the Position property for a specific PivotItem. This is somewhat dangerous because you don't know whether the underlying data will have data for "South" on any given day. Be sure to set Error Checking to resume in case South doesn't exist today:

 On Error Resume Next PT.PivotFields("Region").PivotItems("South").Position = 1 On Error GoTo 0 

    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140 © 2008-2017.
    If you may any questions please contact us: