Amazon VBA User Manual

‘ 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

Amazon VBA Specifications

General

Brand	Amazon
Model	VBA
Category	Software
Language	English