Amazon VBA

255
Handling Additional Annoyances When Creating Your Final Report
‘ 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:=Array(“Product”, “Market”), _
ColumnFields:=”InvoiceDate”
‘ Ensure table layout, with each row field in a new column
PT.RowAxisLayout xlTabularRow
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0,K”
End With
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ Group by Year
WSD.Activate
Cells(3, FinalCol + 4).Group Start:=True, End:=True, _
Periods:=Array(False, False, False, False, False, False, True)
‘ Replace blanks with zero
PT.NullString = “0”
‘ Remove subtotals by product
PT.PivotFields(“Product”).Subtotals(1) = True
PT.PivotFields(“Product”).Subtotals(1) = False
PT.ColumnGrand = False
‘ Sort descending by revenue
PT.PivotFields(“Market”).AutoSort Order:=xlDescending, _
Field:=”Sum of Revenue”
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ PT.TableRange2.Select
‘ 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 = “Revenue by Market and Year”
11
continues
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 255