243
Building a Pivot Table in Excel VBA
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange)
‘ 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:=Array(“Business Segment”, “Product”), _
ColumnFields:=”Region”
‘ 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
‘ Format the pivot table
PT.ShowTableStyleRowStripes = True
PT.TableStyle2 = “PivotStyleMedium10”
End Sub
Learning Why You Cannot Move or Change Part of a Pivot Report
Although pivot tables are incredible, they have annoying limitations. You cannot move or
change just part of a pivot table. For example, try to run a macro that would delete column
X, which contains the Grand Total column of the pivot table. The macro comes to a
screeching halt with an error
1004, as shown in Figure 11.5. To get around this limitation,
you can change the summary from a pivot table to just values using the PasteSpecial method
described below.
11
Figure 11.5
You cannot delete just
part of a pivot table.
12_0789736012_CH11.qxd 12/11/06 6:26 PM Page 243
To Next Page
Loading...
Need help?
General
