PivItem.Visible = True
Case Else
PivItem.Visible = False
End Select
Next PivItem
Controlling the Sort Order Manually
If your company has been reporting regions in the sequence 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
fields 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
Using Sum, Average, Count, Min, Max, and More
So far, every example in this chapter has involved summing data. It is also possible to get an
average, minimum, or maximum of data. In VBA, change the
Function property of the data
field and give the data field a unique name. For example, the following code fragment pro-
duces five different summaries of the revenue field, each with a unique name:
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0,K”
.Name = “Total Revenue”
End With
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlCount
.Position = 2
.NumberFormat = “#,##0”
.Name = “Number Orders”
End With
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
Chapter 11 Using VBA to Create Pivot Tables
276
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 276
To Next Page
Loading...
Need help?
General
