You can generate a Pivot Table report with a mouse click. You can do it using Excel VBA and macros!

Let’s suppose you want to generate a Pivot Table report based on a given Excel table created; see below…

Figure 1: Source data for Pivot Table report

This would be the resulting PT report (a simple one):

Figure 2: simple Pivot Table report

If you do it by hand, this would be the process:

1. Creating the PT report by going to: Table Tools>Design>Summarize with Pivot Table (assuming the Excel table is already created)
2. Setting row fields (“Products”)
3. Setting data fields (“Units Sold”)

Overall, the same steps should be executed using VBA, see the Excel macro code below…

Public Sub CreatePivotTable()
'developed by John Franco
'excelgurusacademy.com
'last update: Mar-03-2012

Dim MyPTReport As PivotTable 'a pivot table report object
Dim MyPTCache As PivotCache 'the pivot table data

On Error Resume Next

'adds a new sheet
Sheets.Add

'creates a pivot table based on Table1. You can change your table name as you like

' Create PivotTable cache and report.
Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

'add row fields
    With MyPTReport.PivotFields("Product")
        .Orientation = xlRowField
        .Position = 1
    End With
'add data fields
    MyPTReport.AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

End Sub

That’s all! You can create a Pivot Table report each time without effort.

Download the Excel Pivot Table macro practice file at the bottom of this page.

Now let me explain the Excel VBA macro code in more detail…

Adding a new Pivot Table using VBA

To create a new Pivot Table in a workbook you need to be familiar with the hierarchy of Pivot Tables objects in the Excel object model, see the picture below…

Figure 3: Pivot Table object model

Basically you need to specify the unambiguous path that points to your PT, e.g.

Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”).PivotTables(“YOURPIVOTTABLE”).”PT method or property here”.

You can make your Excel VBA code shorter when you are referring to the active workbook, replace the entire VBA objects hierarchy: “Workbooks(“YOURBOOK”).Worksheets(“YOURSHEET”)” by this one: “ActiveSheet()…”

Well, to create the Pivot Table invoke the “Add” method of the “PivotTables” collection.

This is the full syntax:

expression.Add(PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

“expression” represents a PivotTables object.

Where the three most important arguments are:

• PivotCache – this provides the data for the report
• TableDestination – the cell where the PT report will be inserted, e.g. “A1”, “G2”, etc.
• TableName –  the name of the Pivot Table, e.g. “Region sales”, “Labor hours summary”, etc.

Here’s the full code I used above:

Set MyPTReport = ActiveSheet.PivotTables.Add(PivotCache:=MyPTCache, TableDestination:=Range("A1"), _
TableName:="PivotTable1")

You first need to specify the source data of your future Pivot Table (cache). To create a “PivotCache”, use the “Create” method of the “PivotCaches” collection.

This is the syntax:

expression.Create(SourceType, SourceData, Version) or you can also use expression.Add(SourceType, SourceData, Version)

“expression” represents a “PivotCaches” object.

Where the two most important arguments are:

• SourceType – can be either: xlConsolidation, xlDatabase, or xlExternal
• SourceData – this is the data source of your Pivot Table, e.g. Range(“A2:F22”), Sheets(“Sheet3”).Range(“F30:G40”), “Table1”, etc.

This is the full code I used above:  

Set MyPTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="Table1")

Now is time to add the respective fields…

Adding row fields to a Pivot Table using VBA

Just for refreshing the location of Pivot Table fields, here’s the PT report layout:

Figure 4: Pivot Table fields’ layout

To add row fields use the “PivotFields” collection of the “PivotTable” object.

Specifically, use the “Orientation” property of the “PivotField” object.

This is the syntax:

expression.Orientation

expression represents a “PivotField” object.

Specify the orientation (field type) using the respective “Orientation” property value:

• xlRowField
• xlPageField
• xlColumnField
• xlDataField

This is the full VBA code I used above…

With MyPTReport.PivotFields("Product")
 .Orientation = xlRowField
 .Position = 1
 End With

At this time you have defined a blank PT report, see below…

Figure 5: Blank Pivot Table report, before adding fields

It’s time to add stuff to it…

Adding data fields to a Pivot Table using VBA

You need to use the “AddDataField” method of the “PivotTable” object.

The syntax is:

expression.AddDataField(Field, Caption, Function)

“expression” represents a “PivotTable” object.

Where the main parameters are:

• Field – what field you refer to, it should be one of the fields contained in the source data (cache). For this example the fields are: “Product”, “Salesman”, “Units Sold” or “Price per unit”
• Caption – how your field will be presented in the Pivot Table report, e.g. “Sum of Sales”, “Average of price”, etc.
• Function – to sum use xlSum, to average use xlAverage, etc.

This is the full code I used above…

MyPTReport.AddDataField ActiveSheet.PivotTables( _
 "PivotTable1").PivotFields("Units Sold"), "Sum of Units Sold", xlSum

Additional VBA Pivot Table techniques

Adding column fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'define column field
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
    .Orientation = xlColumnField
    .Position = 1
End With

Adding page fields using VBA

Use also the “PivotFields” collection, see the full VBA code below…

'add page fields
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
        .Orientation = xlPageField
        .Position = 1
    End With

Converting the current region to a table

If you want to create the Excel Table on which your Pivot Table report will be based on (“Table1” for this example), you need to use the “Add” method of the “ListObjects” collection. See the full VBA code below…

'creates a table based on current selection
ActiveSheet.ListObjects.Add(xlSrcRange, Range(Range("$A$1").CurrentRegion.Address), , xlYes).Name = "Table1"

Excel VBA Downloads

Download the Excel PivotTable macro practice workbook below: