Amazon VBA User Manual

‘ Turn off updating while building the table
PT.ManualUpdate = True
‘ Set up the row fields
PT.AddFields RowFields:=”Customer”, ColumnFields:=”Data”, _
PageFields:=”Region”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0,K”
.Name = “Total Revenue”
End With
‘ Sort customers descending by sum of revenue
PT.PivotFields(“Customer”).AutoSort Order:=xlDescending, _
Field:=”Total Revenue”
‘ Show only the top 5 stores
PT.PivotFields(“Customer”).AutoShow Type:=xlAutomatic, Range:=xlTop, _
Count:=5, Field:=”Total Revenue”
‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
Ctr = 0
‘ Loop through each region
For Each PivItem In PT.PivotFields(“Region”).PivotItems
Ctr = Ctr + 1
PT.PivotFields(“Region”).CurrentPage = PivItem.Name
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ Create a new blank workbook with one worksheet
Set WBN = Workbooks.Add(xlWBATWorksheet)
Set WSR = WBN.Worksheets(1)
WSR.Name = PivItem.Name
‘ Set up Title for Report
With WSR.[A1]
.Value = “Top 5 Customers in the “ & PivItem.Name & “ Region”
.Font.Size = 14
End With
‘ Copy the pivot table data to row 3 of the report sheet
‘ Use offset to eliminate the page & title rows of the pivot table
PT.TableRange2.Offset(3, 0).Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
LastRow = WSR.Cells(65536, 1).End(xlUp).Row
Chapter 11 Using VBA to Create Pivot Tables
274
11
Listing 11.10 Continued
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 274

Amazon VBA Specifications

General

Brand	Amazon
Model	VBA
Category	Software
Language	English