Amazon VBA User Manual

269
Using Advanced Pivot Table Techniques
.Position = 1
.NumberFormat = “#,##0”
.Name = “Total Revenue”
End With
‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”
‘ Sort markets descending by sum of revenue
PT.PivotFields(“Market”).AutoSort Order:=xlDescending, _
Field:=”Total Revenue”
‘ Show only the top 5 markets
PT.PivotFields(“Market”).AutoShow Type:=xlAutomatic, Range:=xlTop, _
Count:=5, Field:=”Total Revenue”
‘ Calc the pivot table to allow the date label to be drawn
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 = “Report”
‘ Set up title for report
With WSR.[A1]
.Value = “Top 5 Markets”
.Font.Size = 14
End With
‘ Copy the pivot table data to row 3 of the report sheet
‘ Use offset to eliminate the title row of the pivot table
PT.TableRange2.Offset(1, 0).Copy
WSR.[A3].PasteSpecial Paste:=xlPasteValuesAndNumberFormats
LastRow = WSR.Cells(65536, 1).End(xlUp).Row
WSR.Cells(LastRow, 1).Value = “Top 5 Total”
‘ Go back to the pivot table to get totals without the AutoShow
PT.PivotFields(“Market”).Orientation = xlHidden
PT.ManualUpdate = False
PT.ManualUpdate = True
PT.TableRange2.Offset(2, 0).Copy
WSR.Cells(LastRow + 2, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
WSR.Cells(LastRow + 2, 1).Value = “Total Company”
‘ Clear the pivot table
PT.TableRange2.Clear
Set PTCache = Nothing
‘ Do some basic formatting
‘ Autofit columns, bold the headings, right-align
WSR.Range(WSR.Range(“A3”), WSR.Cells(LastRow + 2, 10)).Columns.AutoFit
Range(“A3”).EntireRow.Font.Bold = True
Range(“A3”).EntireRow.HorizontalAlignment = xlRight
Range(“A3”).HorizontalAlignment = xlLeft
Range(“A2”).Select
MsgBox “CEO Report has been Created”
End Sub
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 269

Amazon VBA Specifications

General

Brand	Amazon
Model	VBA
Category	Software
Language	English