Controlling the Sort Order Manually

If your company has been reporting regions in the sequence of West, Central, East forever, it is an uphill battle getting managers to accept seeing the report ordered Central, East, 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. They call this a manual sort order. In Figure 12.10, the regions start in an alphabetic sort order of Central, East, West. These headings are in cells L3:N3. To change the sort order in the user interface, you simply go to cell L3—the cell with the word "Central." In that cell, type West and press Enter. As if by magic, Central and East move over and West is now the first column. Of course, all the numbers for West move from column N to Column L. This is not intuitive. See the results in Figure 12.14.

The VBA code to do a manual sort involves setting the Position property for a specific Pivotltem. This is somewhat dangerous because you don't know whether the underlying data will have data for "West" on any given day. Be sure to set Error Checking to resume in case West doesn't exist today (see Chapter 23, "Handling Errors," for more information on error handling): On Error Resume Next

PT.PivotFields("Region").PivotItems("West").Position = 1 On Error GoTo 0

0 0

Post a comment