Amazon VBA

287
Using New Pivot Table Features in Excel 2007
‘ 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 & column fields
PT.AddFields RowFields:=”Branch”, _
ColumnFields:=”Data”
‘ Set up the data fields
With PT.PivotFields(“Revenue”)
.Orientation = xlDataField
.Function = xlSum
.Position = 1
End With
‘ Calc the pivot table
PT.ManualUpdate = False
PT.ManualUpdate = True
‘ Apply a Databar
PT.TableRange2.Cells(3, 2).Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(1).ShowValue = True
Selection.FormatConditions(1).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueLowestValue
.MaxPoint.Modify newtype:=xlConditionValueHighestValue
End With
With Selection.FormatConditions(1).BarColor
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.499984740745262
End With
Selection.FormatConditions(1).ScopeType = xlFieldsScope
WSD.Activate
Range(“R1”).Select
End Sub
11
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 287