macros
How to create a Pivot Table using VBA
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:
- Creating the PT report by going to: Table Tools>Design>Summarize with Pivot Table (assuming the Excel table is already created)
- Setting row fields (“Products”)
- 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:
Excel PivotTable macro practice
How to reference cells dynamically using VBA
The best way to go through cells dynamically is using the Cells object inside a loop. This way you can refer to a different cell on each pass of the loop.
Use a simple counter variable (you can name it whatever you want), which is increased or decreased on each loop pass. Here are some examples…
Using a ForNext loop
Use For Next when you know the starting row/column as well as the last row/column.
For counter = 2 To 5 Cells(counter, 5).Value = Cells(counter, 3).Value * Cells(counter, 4).Value Next
Using a DoWhile loop
Use Do While when you either don’t know the starting row/column or the last row/column.
counter = 2 Do While Cells(counter, 1) <> "" Cells(counter, 5) = Cells(counter, 3).Value * Cells(counter, 4).Value counter = counter + 1 Loop
Using a DoUntil loop
Use Do Until when you either don’t know the starting row/column or the last row/column.
counter = 2 Do Until Cells(counter, 1) = "" Cells(counter, 5) = Cells(counter, 3).Value * Cells(counter, 4).Value counter = counter + 1 Loop