Ensuring Table Layout Is Utilized
In previous versions of Excel, multiple row fields appeared in multiple columns. Three lay-
outs are now available. The Compact layout squeezes all the row fields into a single column.
To prevent this outcome and ensure that your pivot table is in the classic table layout, use
this code:
PT.RowAxisLayout xlTabularRow
Controlling the Sort Order with AutoSort
The Excel user interface offers an AutoSort option that enables you to show markets in
descending order based on revenue. The equivalent code in VBA to sort the product field
by descending revenue uses the
AutoSort method:
PT.PivotFields(“Market”).AutoSort Order:=xlDescending, _
Field:=”Sum of Revenue”
Changing Default Number Format
To change the number format in the user interface, choose a revenue field, click PivotTable
Tools Options, Active Field, Field Settings, Number Format. Then choose an appropriate
number format.
Chapter 11 Using VBA to Create Pivot Tables
248
11
Although the proper code is to set this value to a text zero,Excel actually puts a real zero in the
empty cells.
NOTE
Because of the page break problem, you may find it is easier to do away with the pivot
table’s subtotal rows and have the
Subtotal method add subtotal rows with page breaks.
You need a way to turn off the pivot table subtotal rows offered for Product in Figure
11.8. These rows show up automatically whenever you have two or more row fields. If
you had four row fields, you would want to turn off the automatic subtotals for the
three outermost row fields.
Even with all these problems in default pivot tables, they are still the way to go. You can
overcome each complaint, either by using special settings within the pivot table or by enter-
ing a few lines of code after the pivot table is created and then copied to a regular dataset.
Eliminating Blank Cells in the Values Area
People started complaining about the blank cells immediately when pivot tables were first
introduced. Anyone using Excel 97 or later can easily replace blank cells with zeros. In the
user interface, you can find the setting on the Layout & Format tab of the PivotTable
Options dialog box. Choose the For Empty Cells, Show option and type
0 in the box.
The equivalent operation in VBA is to set the
NullString property for the pivot table
to
“0”.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 248
To Next Page
Loading...
Need help?
General
