Microsoft Office Tutorials and References
In Depth Information
Chapter 17: Working with Pivot Tables
and also to prevent the error. The hand-crafted code that follows generates the same pivot table as
the procedure previously listed:
Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable
‘ Create the cache
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range(“A1”).CurrentRegion)
‘ Add a new sheet for the pivot table
Worksheets.Add
‘ Create the pivot table
Set PT = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=Range(“A3”))
‘ Specify the fields
With PT
.PivotFields(“Region”).Orientation = xlPageField
.PivotFields(“Month”).Orientation = xlColumnField
.PivotFields(“SalesRep”).Orientation = xlRowField
.PivotFields(“Sales”).Orientation = xlDataField
‘no field captions
.DisplayFieldCaptions = False
End With
End Sub
The CreatePivotTable procedure is simplified (and might be easier to understand) because it
declares two object variables: PTCache and PT . A new PivotCache object is created by using
the Create method. A worksheet is added, and it becomes the active sheet (the destination for
the pivot table). Then a new PivotTable object is created by using the Add method of the
PivotTables collection. The last section of the code adds the four fields to the pivot table and
specifies their location within it by assigning a value to the Orientation property.
The original macro hard-coded both the data range used to create the PivotCache object
( ‘Sheet1!R1C1:R13C4’ ) and the pivot table location ( Sheet2 ). In the CreatePivotTable
procedure, the pivot table is based on the current region surrounding cell A1. This ensures that
the macro will continue to work properly if more data is added.
Adding the worksheet before the pivot table is created eliminates the need to hard-code the
sheet reference. Yet another difference is that the hand-written macro doesn’t specify a pivot
table name. Because the PT object variable is created, your code doesn’t ever have to refer to
the pivot table by name.
 
Search JabSto ::




Custom Search