EasyManuals Logo

Amazon VBA User Manual

Amazon VBA
60 pages
To Next Page IconTo Next Page
To Next Page IconTo Next Page
To Previous Page IconTo Previous Page
To Previous Page IconTo Previous Page
Page #55 background imageLoading...
Page #55 background image
285
Using New Pivot Table Features in Excel 2007
Changing the Layout
The Layout group of the Design ribbon contains four drop-downs. These drop-downs con-
trol the location of subtotals (top or bottom), the presence of grand totals, the report layout,
and the presence of blank rows.
Subtotals can appear either at the top or bottom of a group of pivot items. The
SubtotalLocation property applies to the entire pivot table; valid values are xlAtBottom or
xlAtTop:
PT.SubtotalLocation:=xlAtTop
Grand totals can be turned on or off for rows or columns. The following code turns them
off for both:
PT.ColumnGrand = False
PT.RowGrand = False
There are three settings for the report layout. The Tabular layout is similar to the default
layout in Excel 2003. The Outline layout was optionally available in Excel 2003. The
Compact layout is new in Excel 2007.
Excel can remember the last layout used and will apply it to additional pivot tables created
in the same Excel session. For this reason, you should always explicitly choose the layout
that you want. Use the
RowAxisLayout method; valid values are xlTabularRow, xlOutlineRow,
or
xlCompactRow:
11
Previous versions of Excel offered an AutoFormat feature for pivot tables.This feature was annoying
because it actually changed the layout of your pivot table.That obsolete command used the
TableStyle property; hence, Excel 2007 had to use TableStyle2 as the property name for the
new style tables.
NOTE
It is possible to create custom table styles.If you have a custom table style named MyStyle44 and
use this name in a macro,the macro will run fine on your computer but may not run on anyone
elses computer.To alleviate the chance of a runtime error, you use On Error Resume Next
before applying TableStyle2.
CAUTION
To apply a table style from the gallery, use the TableStyle2 property. If you want to get the
correct name, it might be best to record a macro:
‘ Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = “PivotStyleMedium3”
In Figure 11.24, the ToolTip shows a style named Pivot Style Medium 3.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 285

Questions and Answers:

Question and Answer IconNeed help?

Do you have a question about the Amazon VBA and is the answer not in the manual?

Amazon VBA Specifications

General IconGeneral
BrandAmazon
ModelVBA
CategorySoftware
LanguageEnglish

Related product manuals