Amazon VBA User Manual

IN THIS CHAPTER
Using VBA to Create Pivot
Tables
11
Introducing VBA
Version 5 of Excel introduced a powerful new
macro language called Visual Basic for Applications
(VBA). Every copy of Excel shipped since 1993 has
had a copy of the powerful VBA language hiding
behind the worksheets. VBA allows you to perform
steps that you normally perform in Excel, but to
perform them very very quickly and flawlessly. I’ve
seen a VBA program take a process that would take
days each month and turn it into a single button
click and a minute of processing time.
Don’t be intimidated by VBA. The VBA macro
recorder tool will get you 90% of the way to a use-
ful macro and I will get you the rest of the way
there using examples in this chapter.
Every example in this chapter is available for down-
load from http://www.mrexcel.com/
pivot2007data.html/.
Enabling VBA in Your Copy of Excel
By default, VBA is disabled in Office 2007. Before
you can start using VBA, you need to enable
macros in the Trust Center. From the Office icon
menu, choose Excel Options, Trust Center, Trust
Center Settings, Macro Settings.
Choose one of the options below.
 Disable all macros with notification—this set-
ting is equivalent to medium macro security in
Excel 2003. When you open a workbook that
contains macros, a message will appear alerting
that there are macros in the workbook. If you
expect macros to be in the workbook, you sim-
ply click Options, Enable to allow the macros
to run. This is the safest setting, as it forces
you to explicitly enable macros in each work-
book.
Introducing VBA . . . . . . . . . . . . . . . . . . . . . . . .231
Learning Tricks of the Trade . . . . . . . . . . . . . .234
Understanding Versions . . . . . . . . . . . . . . . . .236
Building a Pivot Table in Excel VBA . . . . . . . .239
Creating a Report Showing Revenue
by Product . . . . . . . . . . . . . . . . . . . . . . . . . . . .246
Handling Additional Annoyances When
Creating Your Final Report . . . . . . . . . . . . . . .250
Addressing Issues with Two or More
Data Fields . . . . . . . . . . . . . . . . . . . . . . . . . . . .257
Summarizing Date Fields with Grouping . . .263
Using Advanced Pivot Table Techniques . . . .267
Controlling the Sort Order Manually . . . . . . .276
Using Sum, Average,Count,Min, Max,
and More . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .276
Creating Report Percentages . . . . . . . . . . . . .277
Using New Pivot Table Features in
Excel 2007 . . . . . . . . . . . . . . . . . . . . . . . . . . . . .279
Next Steps . . . . . . . . . . . . . . . . . . . . . . . . . . . . .289
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 231

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