Amazon VBA User Manual

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

Amazon VBA Specifications

General

Brand	Amazon
Model	VBA
Category	Software
Language	English

Summary

Using VBA to Create Pivot Tables

Introducing VBA

Introduction to Visual Basic for Applications (VBA) as a powerful Excel macro language for automation.

Enabling VBA in Your Copy of Excel

Steps to activate VBA macros through Excel's Trust Center settings for security.

Visual Basic Editor

Overview of the VBE interface, including Project Explorer and Code Window.

Visual Basic Tools

Key VBE features like AutoComplete and F1 help for coding.

Learning Tricks of the Trade

Writing Code to Handle Any Size Data Range

Techniques for dynamic range selection using `Cells` and `Resize`.

Understanding Versions

Differences in VBA pivot table methods across Excel versions.

Building a Pivot Table in Excel VBA

Getting a Sum Instead of a Count

Ensuring correct aggregation functions like `xlSum` are explicitly applied in VBA.

Determining Size of a Finished Pivot Table

Using `TableRange2` and `Offset` for accurate pivot table manipulation.

Creating a Report Showing Revenue by Product

Eliminating Blank Cells in the Values Area

Replacing blank pivot table cells with zero using the `NullString` property in VBA.

Controlling the Sort Order with AutoSort

Implementing custom sort orders for pivot table fields via VBA using the `AutoSort` method.

Suppressing Subtotals for Multiple Row Fields

Disabling automatic subtotals for specific pivot fields by setting the `Subtotals` property.

Handling Additional Annoyances When Creating Your Final Report

Creating a Summary on a Blank Report Worksheet

Copying pivot table data as static values to a new sheet for final reporting.

Filling the Outline View

Filling blank cells in column A of the report for consistent presentation and formatting.

Adding Subtotals

Programmatically adding subtotals and page breaks to pivot table reports using VBA.

Addressing Issues with Two or More Data Fields

Calculated Data Fields

Creating custom fields in pivot tables using formulas within VBA.

Calculated Items

Defining new items within a field for combined calculations and analysis.

Summarizing Date Fields with Grouping

Group by Week

Grouping date data by week using VBA's date grouping features.

Using Advanced Pivot Table Techniques

Using AutoShow to Produce Executive Overviews

Filtering pivot tables to display top N records efficiently using the `AutoShow` method.

Using ShowDetail to Filter a Recordset

Drilling down into pivot table cells to view underlying source data records.

Creating Reports for Each Region or Model

Generating individual reports for each item in a page field, like regions or models.

Using New Pivot Table Features in Excel 2007

Using the New Filters

Applying advanced filters (label, date, value) to pivot tables using VBA in Excel 2007.

Applying a Table Style

Formatting pivot tables with predefined styles and layout options via VBA.

Applying a Data Visualization

Incorporating visual elements like data bars into pivot tables using VBA.

Understanding Special Considerations for Excel 97

Pivot Table Code for Excel 97 Users

Using the `PivotTableWizard` method for creating pivot tables compatible with Excel 97.

Next Steps