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

Enable all macros (not recommended; potentially dangerous code can run)—this setting
is equivalent to low macros security in Excel 2003. Because it could allow rogue macros
to run in files that are sent to you by others, Microsoft recommends that you do not use
this setting.
Chapter 11 Using VBA to Create Pivot Tables
232
11
Figure 11.1
Enable the Developer tab
to access the VBA tools.
Visual Basic Editor
Macros Dialog
Macro Recording Tools
Shortcut to Trust Center
Further, when you save your files, you have to save the files as Excel 2007 macro-enabled
workbooks with the .xlsm extension.
Enabling the Developer Ribbon
Most of the VBA tools are located on a Developer tab of the Excel 2007 Ribbon. By default,
this tab is not displayed. To enable it, from the Office icon menu, select Excel Options,
Popular. Then choose Show Developer Tab in the Ribbon.
As shown in Figure 11.1, the Code group on the Developer tab of the Ribbon offers icons
for accessing the Visual Basic Editor, Macros dialog box, macro recording tools, and Macro
Security setting.
Visual Basic Editor
From Excel, press Alt+F11 or choose Developer, Code, Visual Basic to open the Visual
Basic Editor, as shown in Figure 11.2. The three main sections of the VBA Editor are
described here. If this is your first time using VBA, some of these items may be disabled.
Follow the instructions given in the following list to make sure that each is enabled:
Project Explorer—This pane displays a hierarchical tree of all open workbooks.
Expand the tree to see the worksheets and code modules present in the workbook. If
the Project Explorer is not visible, enable it by pressing Ctrl+R.
If you have previously enabled the Developer tab of the Ribbon, you can use the Macro Security icon
to jump quickly to the Trust Center dialog box.
TIP
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 232

233
Introducing VBA
11
Figure 11.2
The Visual Basic Editor
window is lurking
behind every copy of
Excel shipped since 1993.
Project Explorer
Properties window
Code window
Properties window—The Properties window is important when you begin to
program user forms. It has some use when you’re writing normal code, so enable it by
pressing F4.
Code window—This is the area where you write your code. Code is stored in one or
more code modules attached to your workbook. To add a code module to a workbook,
select Insert, Code Module from the application menu.
Visual Basic Tools
Visual Basic is a powerful development environment. Although this chapter cannot offer a
complete course on VBA, if you are new to VBA, you should take advantage of these impor-
tant tools:
As you begin to type code, Excel may offer a drop-down with valid choices. This fea-
ture, known as AutoComplete, allows you to type code faster and eliminate typing mis-
takes.
For assistance on any keyword, put the cursor in the keyword and press F1. You might
need your installation CDs because the VBA help file can be excluded from the installa-
tion of Office 2007.
Excel checks each line of code as you finish it. Lines in error appear in red. Comments
appear in green. You can add a comment by typing a single apostrophe. Use lots of
comments so you can remember what each section of code is doing.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 233

Despite the aforementioned error checking, Excel may still encounter an error at run-
time. If this happens, click the Debug button. The line that caused the error is high-
lighted in yellow. Hover your mouse cursor over any variable to see the current value of
the variable.
When you are in Debug mode, use the Debug menu to step line by line through code.
You can toggle back and forth between Excel and VBA to see the effect of running a
line of code on the worksheet.
Other great debugging tools are breakpoints, the Watch window, the Object Browser,
and the Immediate window. Read about these tools in the Excel VBA Help menu.
The Macro Recorder
Excel offers a macro recorder that is about 90% perfect. Unfortunately, the last 10% is frus-
trating. Code that you record to work with one dataset is hard-coded to work only with that
dataset. This behavior might work fine if your transactional database occupies cells
A1:K415501 every single day, but if you are pulling in a new invoice register every day, it is
unlikely that you will have the same number of rows each day. Given that you might need to
work with other data, it would be a lot better if Excel could record selecting cells using the
End key. This is one of the shortcomings of the macro recorder.
In reality, Excel pros use the macro recorder to record code but then expect to have to clean
up the recorded code.
Understanding Object-Oriented Code
VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax.
However, in VBA, it is called Object.Method. Objects can be workbooks, worksheets, cells,
or ranges of cells. Methods can be typical Excel actions, such as .Copy, .Paste, and
.PasteSpecial.
Many methods allow adverbs—parameters you use to specify how to perform the method. If
you see a construct with a colon/equal sign, you know that the macro recorder is describing
how the method should work.
You also might see the type of code in which you assign a value to the adjectives of an
object. In VBA, adjectives are called properties. If you set ActiveCell.Font.ColorIndex = 3,
you are setting the font color of the active cell to red. Note that when you are dealing with
properties, there is only an equal sign, not a colon/equal sign.
Learning Tricks of the Trade
You need to master a few simple techniques to be able to write efficient VBA code. These
techniques will help you make the jump to writing effective code.
Chapter 11 Using VBA to Create Pivot Tables
234
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 234

235
Learning Tricks of the Trade
Writing Code to Handle Any Size Data Range
The macro recorder hard-codes the fact that your data is in a range, such as A1:K415501.
Although this hard-coding works for today’s dataset, it may not work as you get new
datasets. You need to write code that can deal with different size datasets.
The macro recorder uses syntax such as Range(“H12”) to refer to a cell. However, it is more
flexible to use Cells(12, 8) to refer to the cell in row 12, column 8. Similarly, the macro
recorder refers to a rectangular range as Range(“A1:K415501”). However, it is more flexible to
use the Cells syntax to refer to the upper-left corner of the range and then use the Resize()
syntax to refer to the number of rows and columns in the range. The equivalent way to
describe the preceding range is Cells(1, 1).Resize(415501,11). This approach is more
flexible because you can replace any of the numbers with a variable.
In the Excel user interface, you can use the End key on the keyboard to jump to the end of a
range of data. If you move the cell pointer to the final row on the worksheet and press the
End key followed by the up-arrow key, the cell pointer jumps to the last row with data. The
equivalent of doing this in VBA is to use the following code:
Range(“A1048576”).End(xlUp).Select
You don’t need to select this cell; you just need to find the row number that contains the last
row. The following code locates this row and saves the row number to a variable named
FinalRow:
FinalRow = Range(“A1048576”).End(xlUp).Row
There is nothing magic about the variable name FinalRow. You could call this variable xor
y, or even your dog’s name. However, because VBA allows you to use meaningful variable
names, you should use something such as FinalRow to describe the final row.
11
Excel 2007 offers 1,048,576 rows and 16,384 columns.Excel 97 through Excel 2003 offered 65,536
rows and 256 columns.To make your code flexible enough to handle any versions of Excel,you can
use Rows.Count to learn the total number of rows in this version of Excel.The preceding code
could then be generalized like so:
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
NOTE
You also can find the final column in a dataset. If you are relatively sure that the dataset
begins in row 1, you can use the End key in combination with the left-arrow key to jump
from cell XFD1 to the last column with data. To generalize for the possibility that the code
is running in earlier versions of Excel, you can use the following code:
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 235

End+Down Versus End+Up
You might be tempted to find the final row by starting in cell A1 and using the End key in conjunction with the down-
arrow key.Avoid this approach.Data coming from another system is imperfect. If your program will import 500,000 rows
from a legacy computer system every day for the next five years,a day will come when someone manages to key a null
value into the dataset.This value will cause a blank cell or even a blank row to appear in the middle of your dataset.
Using Range(“A1”).End(xlDown) will stop prematurely at the blank cell instead of including all your data.This
blank cell will cause that day’s report to miss thousands of rows of data,a potential disaster that will call into question
the credibility of your report.Take the extra step of starting at the last row in the worksheet to greatly reduce the risk of
problems.
Using Super-Variables: Object Variables
In typical programming languages, a variable holds a single value. You might use x = 4 to
assign a value of 4 to the variable x.
Think about a single cell in Excel. Many properties describe a cell. A cell might contain a
value such as 4, but the cell also has a font size, a font color, a row, a column, possibly a for-
mula, possibly a comment, a list of precedents, and more. It is possible in VBA to create a
super-variable that contains all the information about a cell or about any object. A statement
to create a typical variable such as x = Range(“A1”) assigns the current value of A1 to the
variable x.
However, you can use the Set keyword to create an object variable:
Set x = Range(“A1”)
You’ve now created a super-variable that contains all the properties of the cell. Instead of
having a variable with only one value, you have a variable in which you can access the value
of many properties associated with that variable. You can reference x.Formula to learn the
formula in A1 or x.Font.ColorIndex to learn the color of the cell.
Understanding Versions
Pivot tables have been evolving. They were introduced in Excel 5 and perfected in Excel 97.
In Excel 2000, pivot table creation in VBA was dramatically altered. Some new parameters
were added in Excel 2002. A few new properties such as PivotFilters and TableStyle2 were
added in Excel 2007. Therefore, you need to be extremely careful when writing code in
Excel 2007 that might be run in Excel 2003 or Excel 2000 or Excel 97.
Just a few simple tweaks make 2003 code run in 2000, but a major overhaul is required to
make any code run in Excel 97. Because it has been 10 years since the release of Excel 97
(and because Microsoft has not supported that product for 5+ years), this chapter focuses on
using only the pivot cache method introduced in Excel 2000. At the end of the chapter, you
briefly learn the PivotTable Wizard method, which is your only option if you need code to
run in Excel 97.
Chapter 11 Using VBA to Create Pivot Tables
236
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 236

237
Understanding Versions
New in Excel 2007
Although the basic concept of pivot tables is the same in Excel 2007 as it was in Excel 2003,
several new features are available in Excel 2007 pivot tables. The entire Design ribbon is
new, including the concepts of subtotals at the top, the report layout options, blank rows,
and the new PivotTable styles. Excel 2007 offers better filters than previous versions. It also
makes the expand and collapse functionality more apparent by adding buttons to the pivot
table grid. Every new feature adds one or more methods or properties to VBA.
If you are hoping to share your pivot table macro with people running prior versions of
Excel, you need to avoid these methods. Your best bet is to open an Excel 2003 workbook in
compatibility mode and record the macro while the workbook is in compatibility mode. If
you are using the macro only in Excel 2007 or later, you can use any of these new features.
Table 11.1 shows the methods that are new in Excel 2007. If you record a macro that uses
these methods, you cannot share the macro with someone using Excel 2003 or earlier.
Table 11.1 Methods New in Excel 2007
Method Description
ClearAllFilters Clears all filters in the pivot table.
ClearTable Removes all fields from the pivot table but keeps
the pivot table intact.
ConvertToFormulas Converts a pivot table to cube formulas. This
method is valid only for pivot tables based on
OLAP data sources.
DisplayAllMemberPropertiesInTooltip Equivalent to Options, Display, Show Properties
in ToolTips.
RowAxisLayout Changes the layout for all fields in the row area.
Valid values are xlCompactRow, xlTabularRow,
or xlOutlineRow.
SubtotalLocation Controls whether subtotals appear at the top or
bottom of each group. Valid arguments are
xlAtTop or xlAtBottom.
Table 11.2 lists the properties that are new in Excel 2007. If you record a macro that refers
to these properties, you cannot share the macro with someone using Excel 2003 or earlier.
Table 11.2 Properties New in Excel 2007
Property Description
ActiveFilters Indicates the active filters in the pivot table; this
is a read-only property.
11
continues
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 237

AllowMultipleFilters Indicates whether a pivot field can have multiple
filters applied to it at the same time.
CompactLayoutColumnHeader Specifies the caption that is displayed in the col-
umn header of a pivot table when in compact
row layout form.
CompactLayoutRowHeader Specifies the caption that is displayed in the row
header of a pivot table when in compact row
layout form.
CompactRowIndent Indicates the indent increment for pivot items
when compact row layout form is turned on.
DisplayContextTooltips Controls whether ToolTips are displayed for
pivot table cells.
DisplayFieldCaptions Controls whether filter buttons and pivot field
captions for rows and columns are displayed in
the grid.
DisplayMemberPropertyTooltips Controls whether to display member properties
in ToolTips.
FieldListSortAscending Controls the sort order of fields in the
PivotTable Field List. When this property is
True, the fields are sorted in alphabetical order.
When it is set to False, the fields are presented
in the same sequence as the data source
columns.
InGridDropZones Controls whether you can drag and drop fields
onto the grid. Changing the pivot table layout
also changes this property. Changing this pro-
erty forces the layout back to a table layout.
LayoutRowDefault Specifies the layout settings for pivot fields
when they are added to the pivot table for the
first time. Valid values are xlCompactRow,
xlTabularRow, or xlOutlineRow.
PivotColumnAxis Returns a PivotAxis object representing the
entire column axis.
PivotRowAxis Returns a PivotAxis object representing the
entire row axis.
PrintDrillIndicators Specifies whether drill indicators are printed
with the pivot table.
Chapter 11 Using VBA to Create Pivot Tables
238
11
Table 11.2 Continued
Property Description
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 238

239
Building a Pivot Table in Excel VBA
ShowDrillIndicators Specifies whether drill indicators are shown in
the pivot table.
ShowTableStyleColumnHeaders Controls whether table style 2 should affect the
column headers.
ShowTableStyleColumnStripes Controls whether table style 2 should show
banded columns.
ShowTableStyleLastColumn Controls whether table style 2 should format the
final column.
ShowTableStyleRowHeaders Controls whether table style 2 should affect the
row headers.
ShowTableStyleRowStripes Controls whether table style 2 should show
banded columns.
SortUsingCustomLists Controls whether custom lists are used for sort-
ing items of fields, both initially and later when
applying a sort. Setting this property to False
can optimize performance for fields with many
items and allows you to avoid using custom-list
based sorting.
TableStyle2 Specifies the pivot table style currently applied
to the pivot table. Note that previous versions of
Excel offered a weak AutoFormat option. That
feature’s settings were held in the TableStyle
property, so Microsoft had to use TableStyle2
as the property name for the new pivot table
styles. The property might have a value such as
PivotStyleLight17.
Building a Pivot Table in Excel VBA
In this chapter, we do not mean to imply that you use VBA to build pivot tables to give to
your clients. Rather, the purpose of this chapter is to remind you that pivot tables can be
used as a means to an end; you can use a pivot table to extract a summary of data and then
use that summary elsewhere.
11
Property Description
The code listings from this chapter are available for download at http://www.MrExcel.com/
pivot2007data.html.
TIP
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 239

Chapter 11 Using VBA to Create Pivot Tables
240
11
In Excel 2000 and newer, you first build a pivot cache object to describe the input area of
the data:
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets(“PivotTable”)
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
‘ Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
After defining the pivot cache, use the CreatePivotTable method to create a blank pivot
table based on the defined pivot cache:
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _
TableName:=”PivotTable1”)
In the CreatePivotTable method, you specify the output location and optionally give the
table a name. After running this line of code, you have a strange-looking blank pivot table,
like the one shown in Figure 11.3. You now have to use code to drop fields onto the table.
If you choose the Defer Layout Update setting in the user interface to build the pivot table,
Excel does not recalculate the pivot table after you drop each field onto the table. By default
in VBA, Excel calculates the pivot table as you execute each step of building the table. This
could require the pivot table to be executed a half-dozen times before you get to the final
result. To speed up your code execution, you can temporarily turn off calculation of the
pivot table by using the ManualUpdate property:
PT.ManualUpdate = True
Although the Excel user interface has new names for the various sections of a pivot table,VBA code
will continue to refer to the old names.Microsoft had to use this choice, otherwise millions of lines
of code would stop working in Excel 2007 when they referred to a page field instead of a filter field.
While the four sections of a pivot table in the Excel user interface are Report Filter, Column Labels,
Row Labels, and Values,VBA continues to use the old terms of Page fields,Column fields,Row fields,
and Data fields.
CAUTION
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 240

241
Building a Pivot Table in Excel VBA
You can now run through the steps needed to lay out the pivot table. In the .AddFields
method, you can specify one or more fields that should be in the row, column, or filter area
of the pivot table.
The RowFields parameter enables you to define fields that appear in the Row Labels layout
area of the PivotTable Field List. The ColumnFields parameter corresponds to the Column
Labels layout area. The PageFields parameter corresponds to the Report Filter layout area.
The following line of code will populate a pivot table with two fields in the row area and
one field in the column area.
‘ Set up the row & column fields
PT.AddFields RowFields:=Array(“Business Segment”, “Product”), _
ColumnFields:=”Region”
To add a field such as Revenue to the values area of the table, you change the Orientation
property of the field to be xlDataField.
Getting a Sum Instead of a Count
Excel is smart. When you build a report with revenue, it assumes you want to sum the rev-
enue. But there is a problem. Say that one of the revenue cells is blank. When you build the
pivot table, even though 99.9% of fields are numeric, Excel assumes you have alphanumeric
data and offers to count this field. This is annoying. It seems to be an anomaly that, on one
hand, you are expected to make sure that 100% of your cells have numeric data, but on the
other hand, the results of the pivot table are often filled with non-numeric blank cells.
When you build the pivot table in the Excel interface, you should take care in the Values
drop zone to notice that the field reads Count of Revenue instead of Sum of Revenue. At
that point, the right course of action is to go back and fix the data, but what people usually
do is double-click the Count of Revenue button and change it to Sum of Revenue.
In VBA, you should always explicitly define that you are creating a sum of revenue by
explicitly setting the Function property to xlSum:
11
Figure 11.3
Immediately after you
use the
CreatePivotTable
method,Excel gives you
a four-cell blank pivot
table that is not very
useful.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 241

‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
At this point, you’ve given VBA all the settings required to correctly generate the pivot
table. If you set ManualUpdate to False, Excel calculates and draws the pivot table. You can
immediately thereafter set this back to True:
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
Your pivot table inherits the table style settings selected as the default on whatever computer
happens to run the code. If you would like control over the final format, you can explicitly
choose a table style. The following code applies banded rows and a medium table style:
‘ Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = “PivotStyleMedium10”
At this point, you have a complete pivot table like the one shown in Figure 11.4.
Chapter 11 Using VBA to Create Pivot Tables
242
11 Figure 11.4
Fewer than 50 lines of
code create this pivot
table in less than a sec-
ond.
Listing 11.1 shows the complete code used to generate the pivot table.
Listing 11.1 Code to Generate a Pivot Table
Sub CreatePivot()
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets(“PivotTable”)
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
‘ Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count). _
End(xlToLeft).Column
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 242

243
Building a Pivot Table in Excel VBA
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)
‘ Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:=”PivotTable1”)
‘ Turn off updating while building the table
PT.ManualUpdate = True
‘ Set up the row & column fields
PT.AddFields RowFields:=Array(“Business Segment”, “Product”), _
ColumnFields:=”Region”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = “PivotStyleMedium10”
End Sub
Learning Why You Cannot Move or Change Part of a Pivot Report
Although pivot tables are incredible, they have annoying limitations. You cannot move or
change just part of a pivot table. For example, try to run a macro that would delete column
X, which contains the Grand Total column of the pivot table. The macro comes to a
screeching halt with an error 1004, as shown in Figure 11.5. To get around this limitation,
you can change the summary from a pivot table to just values using the PasteSpecial method
described below.
11
Figure 11.5
You cannot delete just
part of a pivot table.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 243

Determining Size of a Finished Pivot Table
Knowing the size of a pivot table in advance is difficult. If you run a report of transactional
data on one day, you may or may not have sales from the West region, for example. This
could cause your table to be either six or seven columns wide. Therefore, you should use the
special property TableRange2 to refer to the entire resultant pivot table.
Because of the limitations of pivot tables, you should generally copy the results of a pivot
table to a new location on the worksheet and then delete the original pivot table. The code
in CreateSummaryReportUsingPivot() creates a small pivot table. Note that you can set the
ColumnGrand and RowGrand properties of the table to False to prevent the totals from being
added to the table.
PT.TableRange2 includes the entire pivot table. In this case, this includes the extra row at the
top with the button Sum of Revenue. To eliminate that row, the code copies PT.TableRange2
but offsets this selection by one row by using .Offset(1, 0). Depending on the nature of
your pivot table, you might need to use an offset of two or more rows to get rid of extrane-
ous information at the top of the pivot table.
The code copies PT.TableRange2 and uses PasteSpecial on a cell five rows below the cur-
rent pivot table. At that point in the code, your worksheet appears as shown in Figure 11.6.
The table in R2 is a live pivot table, and the table in R10 is just the copied results.
Chapter 11 Using VBA to Create Pivot Tables
244
11 Figure 11.6
An intermediate result of
the macro.Only the sum-
mary in R10:V14 will
remain after the macro
finishes.
You can then totally eliminate the pivot table by applying the Clear method to the entire
table. If your code is then going on to do additional formatting, you should remove the
pivot cache from memory by setting PTCache equal to Nothing.
The following code will use a pivot table to produce a summary from the underlying data.
At the end of the code, the pivot table will be copied to static values and the pivot table will
be cleared.
Listing 11.2 Code to Produce a Static Summary from a Pivot Table
Sub CreateSummaryReportUsingPivot()
‘ Use a Pivot Table to create a static summary report
‘ with model going down the rows and regions across
Dim WSD As Worksheet
Dim PTCache As PivotCache
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 244

245
Building a Pivot Table in Excel VBA
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Set WSD = Worksheets(“PivotTable”)
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range(“R1:AZ1”).EntireColumn.Clear
‘ Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Columns.Count). _
End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
‘ Create the Pivot Table from the Pivot Cache
Set PT = PTCache.CreatePivotTable(TableDestination:=WSD. _
Cells(2, FinalCol + 2), TableName:=”PivotTable1”)
‘ Turn off updating while building the table
PT.ManualUpdate = True
‘ Set up the row fields
PT.AddFields RowFields:=”Business Segment”, ColumnFields:=”Region”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
With PT
.ColumnGrand = False
.RowGrand = False
.NullString = “0”
End With
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ PT.TableRange2 contains the results. Move these to R10
‘ as just values and not a real pivot table.
PT.TableRange2.Offset(1, 0).Copy
WSD.Cells(5 + PT.TableRange2.Rows.Count, FinalCol + 2). _
PasteSpecial xlPasteValues
‘ At this point, the worksheet looks like Figure 11.6
‘ Delete the original Pivot Table & the Pivot Cache
PT.TableRange2.Clear
Set PTCache = Nothing
11
continues
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 245

WSD.Activate
Range(“R1”).Select
End Sub
The preceding code creates the pivot table. It then copies the results as values and pastes
them as values in R10:V13. Figure 11.6 shows an intermediate result just before the original
pivot table is cleared.
So far, this chapter has walked you through building the simplest of pivot table reports.
Pivot tables offer far more flexibility. Read on for more complex reporting examples.
Creating a Report Showing Revenue by Product
A typical report might provide a list of markets by product with revenue by year. This
report could be given to product line managers to show them which markets are selling
well. In this example, you want to show the markets in descending order by revenue with
years going across the columns. A sample report is shown in Figure 11.7.
Chapter 11 Using VBA to Create Pivot Tables
246
11 Figure 11.7
A typical request is to
take transactional data
and produce a summary
by product for product
line managers.You can
use a pivot table to get
90% of this report and
then a little formatting
to finish it.
Listing 11.2 Continued
The key to producing this data quickly is to use a pivot table. Although pivot tables are
incredible for summarizing data, they are quirky and their presentation is downright ugly.
The final result is rarely formatted in a manner that is acceptable to line managers. There is
not a good way to insert page breaks between each product in the pivot table.
To create this report, start with a pivot table that has Product and Market as row fields,
Invoice Date grouped by year as a column field, and Sum of Revenue as the data field.
Figure 11.8 shows the default pivot table created with these settings.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 246

247
Creating a Report Showing Revenue by Product
Here are just a few of the annoyances that most pivot tables present in their default state:
The Outline view is horrible. In Figure 11.8, the value Cleaning & Housekeeping
Services appears in the product column only once and is followed by 12 blank cells.
This is the worst feature of pivot tables, and there is absolutely no way to correct it.
Although humans can understand that this entire section is for Cleaning sales, it is radi-
cally confusing if your Cleaning section spills to a second or third page. Page 2 starts
without any indication that the report is for Cleaning sales. If you intend to repurpose
the data, you need the Cleaning sales value to be on every row.
The report contains blank cells instead of zeros. In Figure 11.8, the Florida market had
no cleaning sales in 2006. Excel produces a pivot table where cell T9 is blank instead of
zero. This is simply bad form. Excel experts rely on being able to “ride the range,”
using the End and arrow keys. Blank cells ruin this ability.
The title is boring. Most people would agree that Sum of Revenue is an annoying title.
Some captions are extraneous. Invoice Date floating in cell T2 of Figure 11.8 really
does not belong in a report.
The default alphabetical sort order is rarely useful. Product line managers are going to
want the top markets at the top of the list. It would be helpful to have the report sorted
in descending order by revenue.
The borders are ugly. Excel draws in a myriad of borders that really make the report
look awful.
The default number format is General. It would be better to set this up as data with
commas to serve as thousands separators, or perhaps even data in thousands or millions.
Pivot tables offer no intelligent page break logic. If you want to be able to produce one
report for each Line of Business manager, there is no fast method for indicating that
each product should be on a new page.
11
Figure 11.8
Use the power of the
pivot table to get the
summarized data,but
then use your own com-
mon sense in formatting
the report.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 247

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 0in 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

249
Creating a Report Showing Revenue by Product
When you have large numbers, displaying the thousands separator helps the person reading
the report. To set up this format in VBA code, use the following:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0”
Some companies have customers who typically buy thousands or millions of dollars’ worth
of goods. You can display numbers in thousands by using a single comma after the number
format. Of course, you need to include a Kabbreviation to indicate that the numbers are in
thousands:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0,K”
Of course, local custom dictates the thousands abbreviation. If you are working for a rela-
tively young computer company where everyone uses Kfor the thousands separator, you’re
in luck because Microsoft makes it easy to use this abbreviation. However, if you work at a
100+ year-old soap company where you use Mfor thousands and MM for millions, you have a
few more hurdles to jump. You are required to prefix the Mcharacter with a backslash to
have it work:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0,\M”
Alternatively, you can surround the Mcharacter with double quotation marks. To put double
quotation marks inside a quoted string in VBA, you must put two sequential quotation
marks. To set up a format in tenths of millions that uses the #,##0.0,,”MM” format, you
would use this line of code:
PT.PivotFields(“Sum of Revenue”).NumberFormat = “#,##0.0,, “”M”””
Here, the format is quotation mark, pound, comma, pound, pound, zero, period, zero,
comma, comma, quotation mark, quotation mark, M, quotation mark, quotation mark, quo-
tation mark. The three quotation marks at the end are correct. You use two quotation marks
to simulate typing one quotation mark in the custom number format box and a final quota-
tion mark to close the string in VBA.
Suppressing Subtotals for Multiple Row Fields
As soon as you have more than one row field, Excel automatically adds subtotals for all but
the innermost row field. However, you may want to suppress subtotals for any number of
reasons. Although accomplishing this task manually may be relatively simple, the VBA code
to suppress subtotals is surprisingly complex.
You must set the Subtotals property equal to an array of 12 False values. Read the VBA
help for all the gory details, but it goes something like this: The first False turns off auto-
matic subtotals, the second False turns off the Sum subtotal, the third False turns off the
Count subtotal, and so on. It is interesting that you have to turn off all 12 possible subtotals,
even though Excel displays only one subtotal. This line of code suppresses the Product
subtotal:
PT.PivotFields(“Product”).Subtotals = Array(False, False, False, False, _
False, False, False, False, False, False, False, False)
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 249

A different technique is to turn on the first subtotal. This method automatically turns off
the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals
are suppressed:
PT.PivotFields(“Product”).Subtotals(1) = True
PT.PivotFields(“Product”).Subtotals(1) = False
Suppressing Grand Total for Rows
Because you are going to be using VBA code to add automatic subtotals, you can get rid of
the Grand Total row. If you turn off Grand Total for Rows, you delete the column called
Grand Total. Thus, to get rid of the Grand Total row, you must uncheck Grand Total for
Columns. This is handled in the code with the following line:
PT.ColumnGrand = False
Handling Additional Annoyances When Creating Your Final
Report
You’ve reached the end of the adjustments that you can make to the pivot table. To achieve
the final report, you have to make the remaining adjustments after converting the pivot
table to regular data.
Figure 11.9 shows the pivot table with all the adjustments described in the preceding sec-
tions and with PT.TableRange2 selected.
Chapter 11 Using VBA to Create Pivot Tables
250
11
Figure 11.9
Getting 90% of the way
to the final report took
less than one second and
fewer than 30 lines of
code.To solve the last
five annoying problems,
you have to change this
data from a pivot table
to regular data.
Creating a New Workbook to Hold the Report
Say you want to build the report in a new workbook so that it can be easily mailed to the
product managers. Doing this is fairly easy. To make the code more portable, assign object
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 250