Amazon VBA

‘ 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:=”Product”
‘ Define calculated item along the product dimension
PT.PivotFields(“Product”).CalculatedItems _
.Add “Plants Group”, _
“=’Landscaping/Grounds Care’+’Green Plants and Foliage Care’”
‘ Resequence so that the report Landscaping First
PT.PivotFields(“Product”). _
PivotItems(“Landscaping/Grounds Care”).Position = 1
PT.PivotFields(“Product”). _
PivotItems(“Green Plants and Foliage Care”).Position = 2
PT.PivotFields(“Product”). _
PivotItems(“Plants Group”).Position = 3
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0”
End With
‘ 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
WSD.Activate
Range(“R1”).Select
End Sub
Chapter 11 Using VBA to Create Pivot Tables
262
11
Listing 11.5 Continued
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 262