Excel VBA Pivot Tables
In this article I will explain how you can use VBA for Excel to create pivot tables.
Contents
Create New Pivot Table:
The following code will create an empty pivot table. The function receives as input a source range, and an output range. The source range is the range where the pivot tables data comes from. The output range is the where the pivot table will be created. The output range is basically just one cell:
''' 'Creates a pivot table 'rngSource: The source range for the pivot table 'rngDestincation: The destination range for the pivot table 'The function returns a pivot table object Public Function AddPivotTable(ByRef rngSource As Range, ByRef rngDestination As Range) As PivotTable Dim strSourceSheetName As String Dim strSourceRange As String Dim objPivotCache As PivotCache Dim objPivotTable As PivotTable Dim strDestSheetName As String Dim strDestRange As String Dim objWorkbook As Workbook 'the name of the source worksheet strSourceSheetName = rngSource.Worksheet.Name 'the string representation of the source range strSourceRange = strSourceSheetName & "!" & rngSource.Address 'the name of the destination worksheet strDestSheetName = rngDestination.Worksheet.Name 'the string representation of the destination worksheet strDestRange = strDestSheetName & "!" & rngDestination.Address 'the workbook of the source worksheet Set objWorkbook = rngSource.Worksheet.Parent 'creates a new pivot table Set objPivotTable = objWorkbook.PivotCaches.Create(xlDatabase, strSourceRange, xlPivotTableVersion14).CreatePivotTable( _ rngDestination, "PivotTable5", , xlPivotTableVersion14) 'returns the newly created workbook Set AddPivotTable = objPivotTable End Function
Calling Context:
Dim objPivotTable As PivotTable Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1"))
Consider the following data: By using the example above an empty pivot table will be created in sheet2:
Adding Row Labels:
You can add row labels to the pivot table using the code below:
Dim objPivotTable As PivotTable Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1")) With objPivotTable.PivotFields("Date") .Orientation = xlRowField .Position = 1 End With
In the code above “Date” is the field that will become the row label.
Result:
Adding Column Labels:
You can add column labels to the pivot table using the code below:
Dim objPivotTable As PivotTable Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1")) With objPivotTable.PivotFields("Last Name") .Orientation = xlColumnField .Position = 1 End With
Where “Last Name” is the field that will become the column label.
Result:
Adding Page Filters:
You can add page filters to the pivot table using the code below:
Dim objPivotTable As PivotTable Set objPivotTable = AddPivotTable(Sheet1.Range("A1:F21"), Sheet2.Range("A1")) With objPivotTable.PivotFields("Last Name") .Orientation = xlPageField .Position = 1 End With
Where “Last Name” is the field that will become the page filter.
Result:
Adding Value Fields:
The best method to adding value fields would be to use the macro recorder to get the required code. Then either replace ActiveSheet.PivotTables("PivotTable5")
with the pivot table object, or ensure that the worksheet with the pivot table is the active sheet.
Filtering Fields:
Consider the following pivot table on sheet2. Lets say we want to filter the row labels to only show Anthony and Aura:
The code below will filter all fields except Anthony and Aura:
Dim objPivotTable As PivotTable Dim i As Long Set objPivotTable = Sheet2.PivotTables.Item(1) 'loop through the pivot items For i = 1 To objPivotTable.PivotFields("First Name").PivotItems.Count 'check their value If objPivotTable.PivotFields("First Name").PivotItems(i).Name <> "Augustina" And _ objPivotTable.PivotFields("First Name").PivotItems(i).Name <> "Aura" Then objPivotTable.PivotFields("First Name").PivotItems(i).Visible = False Else objPivotTable.PivotFields("First Name").PivotItems(i).Visible = True End If Next i