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.