Page 383 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
PivotTables and PivotCharts

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.

Sub CreatePivotTable()

Dim pc As PivotCache
Dim ws As Worksheet
Dim pt As PivotTable

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "PivotTableSheet" Then
ws.Delete

End If
Next ws

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
Chapter 16
pt.PivotFields("Hour").Orientation = xlColumnField
pt.PivotFields("Hour").Position = 1

pt.AddDataField pt.PivotFields("Sales"), "Sum of Sales", xlSum

End Sub
A simple For Each loop is used to scan through the collection of Worksheet 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 Worksheets 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.

357