Programming Excel with VBA and .NET

Create Pivot Tables

There are several ways to create a pivot table in code:

• Use the PivotTableWizard method to quickly create a pivot table from a Worksheet or PivotTable object.

• Use the Workbook object’s PivotCache collection to create a new pivot table one step at a time. This is sometimes clearer than using the PivotTableWizard method.

• Use an existing PivotCache object’s CreatePivotTable method to create a pivot table that shares the cache of an existing pivot table.

Tip

If you are programming for multiple Excel versions, be sure to test any code using the PivotCache object on the earliest version of Excel you plan to support. A large number of that object’s members were added in 2002.

To quickly create a new pivot table in code:

1. Call the Worksheet object’s CreatePivotTableWizard method.

2. Set the layout of the fields on the pivot table.

For example, the following code creates a pivot table from data on the current worksheet:

Sub QuickPivotTable(  )
    Dim pt As PivotTable
    ' Exit if active sheet is not a worksheet
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    ' Use pivot table wizard to create table on new worksheet.
    Set pt = ActiveSheet.PivotTableWizard(xlDatabase, ActiveSheet.UsedRange, _
      Worksheets.Add.[a3], "QuickPivot")
    ' Select the table so user can set layout.
    pt.TableRange1.Select
End Sub

Selecting the pivot table range in the last line of code displays the PivotTable Field List from which the user can drag items onto the new pivot table.

Pivot tables are created from an underlying ...