Microsoft Excel —

Build a Pivot Table in Excel VBA

Keep in mind that this tutorial is not meant to imply that you use VBA to build pivot tables to give to your users! Rather, its purpose is to remind you that pivot tables can be used as a means to an end; you can use a pivot table to extract a summary of data and then use that summary elsewhere.

In Excel 2000 and newer, you first build a pivot cache object to describe the input area of the data:

Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
Set WSD = Worksheets("PivotTable")

' Delete any prior pivot tables
For Each PT In WSD.PivotTables
    PT.TableRange2.Clear
Next PT

' Define input area and set up a Pivot Cache
FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:=PRange)

After the pivot cache is defined, use the CreatePivotTable method to create a blank pivot table based on the defined pivot cache:

Set PT = PTCache.CreatePivotTable(TableDestination:=WSD.Cells(2, FinalCol + 2), _
    TableName:="PivotTable1")

In the CreatePivotTable method, you specify the output location and optionally give the table a name. After running this line of code, you have a strange-looking blank pivot table, like the one shown in Figure 2.

2. Immediately after you use the CreatePivotTable method, Excel gives you a four-cell blank pivot table that is not very useful. You now have to use code to drop fields onto the table.



If you are using the Layout dialog box in the user interface to build the pivot table, Excel does not recalculate the pivot table after you drop each field onto the table. By default in VBA, Excel calculates the pivot table as you execute each step of building the table. This could require the pivot table to be executed a half-dozen times before you get to the final result. To speed up your code execution, you can temporarily turn off calculation of the pivot table by using the ManualUpdate property:

PT.ManualUpdate = True

You can now run through the steps needed to lay out the pivot table. In the .AddFields method, you can specify one or more fields that should be in the row, column, or page area of the pivot table:

' Set up the row & column fields
PT.AddFields RowFields:=Array("Line of Business", "Model"), _
    ColumnFields:="Region"

To add a field such as Revenue to the data area of the table, you change the Orientation property of the field to be xlDataField.