271
Using Advanced Pivot Table Techniques
‘ Delete any prior pivot tables
For Each PT In WSD.PivotTables
PT.TableRange2.Clear
Next PT
WSD.Range(“R1:AZ1”).EntireColumn.Clear
‘ 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:=”Customer”, ColumnFields:=”Data”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = “#,##0”
.Name = “Total Revenue”
End With
‘ Sort Stores descending by sum of revenue
PT.PivotFields(“Customer”).AutoSort Order:=xlDescending, _
Field:=”Total Revenue”
‘ Show only the top 3 stores
PT.PivotFields(“Customer”).AutoShow Type:=xlAutomatic, Range:=xlTop, _
Count:=3, Field:=”Total Revenue”
‘ Ensure that we get zeros instead of blanks in the data area
PT.NullString = “0”
‘ Calc the pivot table to allow the date label to be drawn
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ Produce summary reports for each customer
For i = 1 To 3
PT.TableRange2.Offset(i + 1, 1).Resize(1, 1).ShowDetail = True
‘ The active sheet has changed to the new detail report
‘ Add a title
Range(“A1:A2”).EntireRow.Insert
Range(“A1”).Value = “Detail for “ & _
PT.TableRange2.Offset(i + 1, 0).Resize(1, 1).Value & _
“ (Customer Rank: “ & i & “)”
Next i
11
continues
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 271
To Next Page
Loading...