Microsoft Office Tutorials and References
In Depth Information
Creating a PivotTable
The code to create a simple PivotTable is shown the following listing. The routine begins by
declaring temporary objects to hold references to the PivotCache and the PivotTable objects,
plus a temporary variable that will be used to delete the worksheet containing the PivotTable.
Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "PivotTableSheet" Then
Set ws = ActiveWorkbook.Worksheets.Add()
ws.Name = "PivotTableSheet"
Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, "AllData!R1C1:R1117C6")
Set pt = pc.CreatePivotTable("PivotTableSheet!R1C1", "My Pivot Table")
pt.PivotFields("Month").Orientation = xlRowField
pt.PivotFields("Month").Position = 1
pt.PivotFields("Hour").Orientation = xlColumnField
pt.PivotFields("Hour").Position = 1
pt.AddDataField pt.PivotFields("Sales"), "Sum of Sales", xlSum
A simple For Each loop is used to scan through the collection of Wo rksheet objects associated
with the active workbook, looking for a worksheet named PivotTableSheet . If the worksheet is
found, the worksheet is deleted. After the worksheet is deleted, a new worksheet with the
same name is added to the Wo rksheets collection. This code ensures that the worksheet is
empty before the PivotTable is added.
Next a PivotCache object is created using the PivotCaches.Add method. The xlDatabase argu
ment indicates that the data is formatted as a series of rows and columns, while the second
parameter indicates that the data is located on the AllData worksheet in columns 1 to 6 and
rows 1 to 1117.