What is Excel VBA Pivot Table?
VBA Pivot Table is a feature available to summarize large data sets and create summarized and consolidated reports. VBA Pivot Table allows us to automate creating a pivot table and makes it easier to slice the data with just the click of a button.
For example, if you have sales data spread across 1 million rows, then using the VBA Pivot Table, we can create a consolidated city-wise sales report as follows.
However, creating a VBA Pivot table is more challenging than creating a pivot table in an Excel worksheet. We will take you through that process in detail in this article.
Table of contents
Key Takeaways
- VBA Pivot Table is used to automate the creation of pivot tables with specified fields and columns to create a summarized report.
- VBA Pivot Table can be created in a separate sheet and in the same sheet.
- We can use the orientation of the pivot table fields section to specify the location of the columns from the data field.
- We can summarize the data based on the row fields and column fields we mention.
Steps to Create Pivot Table in VBA
There are various steps involved in creating a VBA Pivot Table, and we will show you a step-by-step approach to writing code for creating a Pivot Table in VBA.
We will use the following data to create a VBA Pivot Table, you can download the Excel file from the given link and start following the steps.
- Create a macro sub-procedure by naming the macro.
- The VBA Pivot Table is an object reference; hence, we must define a variable and assign PivotTable as an Object data type.
- When the pivot table is created, it creates a pivot table cache in the worksheet. However, in VBA, we must initiate this through a variable.
Define a variable and assign the PivotCache reference.
- Since we must create a VBA PivotTable in a new sheet, we must create a separate sheet. Hence, define a variable with a worksheet object reference.
- We need a data sheet reference; hence, define a variable with a worksheet.
- Define two variables to find the last used row (LR) and last used column (LC).
- Define a variable to set the data Range reference.
It concludes the defining of variables which are essential to create a VBA Pivot Table. Next, we will set references to these defined variables. - First, we must set the reference for the data worksheet using the variable “DS.” In this example, we have our data in the “Projects” worksheet; hence, we can set the worksheet reference as follows.
- Next, we must dynamically find the data range in the data worksheet. We must find the last used row and column using the variables LR and LC for this.
- Once we know the last used row and column, we can set the data range reference to the variable “DR.”
We can now refer to the data range using the variable DR (Data Range). - Next, we must create a new worksheet to insert a pivot table and name it “Sales Summary Sheet.” To do this, we must first delete the same worksheet if there are any.
There is one problem with this code, i.e., if we try to delete a worksheet that does not exist, we will get the error. We must wrap the above code inside the “On Error” statement to avoid this.
Another thing we must do is turn off display alerts, i.e., whenever we try to delete the worksheet, we generally get the following alert message box.
To avoid this, we use the following codes to turn off alerts.
- Now set the reference for the pivot table worksheet inserted in the previous step to the variable “PS” (Pivot Sheet).
- Next, we must create a pivot cache using the variable “PC.”
- We can use the pivot cache variable and create a pivot table.
It will create a blank pivot table as follows.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Once the VBA Pivot Table is created, we can play around with the rows and columns to create a summarized report.
We can see these examples in the example section of the article. You can use the following code to create a blank pivot table.
Sub VBA_Pivot_Table()
'Define variables necessary for VBA Pivot Table creation
Dim PT As PivotTable
Dim PC As PivotCache
Dim PS As Worksheet
Dim DS As Worksheet
Dim LR As Long
Dim LC As Long
Dim DR As Range
'Set the data worksheet reference to variable
Set DS = Worksheets("Projects")
'Find the last used row (LR) and last used column (LC) of the data range
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
'Set the data range using the LR and LC
Set DR = DS.Cells(1, 1).Resize(LR, LC)
On Error Resume Next
'Turn off alert message boxes
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Delete the pivot table to be inserted sheet before inserting one
Worksheets("Sales Summary Sheet").Delete
'Insert a new sheet
Worksheets.Add After:=Worksheets("Projects")
'Rename the sheet as "Sales Summary Sheet"
ActiveSheet.Name = "Sales Summary Sheet"
On Error GoTo 0
'Set the pivot table worksheet reference to variable
Set PS = Worksheets("Sales Summary Sheet")
'Set Pivot Cache
Set PC = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=DR)
'Create blank Pivot Table object
Set PT = PC.CreatePivotTable(TableDestination:=PS.Cells(1, 1), TableName:="Sales_Summary")End Sub
Examples
We will use the same data and create summarized reports.
Example #1 – Create Project-Wise Credits
Once the blank pivot table has been inserted, we can utilize that pivot table and create a report out of it. One such example is creating project-wise credit.
In the previous example, we named the pivot table “Sales_Summary” in the worksheet “Sales Summary Sheet.” So now, to refer to the same VBA Pivot Table, we need to use both the worksheet name and the pivot table name.
In the previous example we have named the pivot table as “Sales_Summary” in the worksheet “Sales Summary Sheet”. So now to refer to the same VBA Pivot Table we need to use both the worksheet name and the pivot table name.
PS is the variable that references the pivot table worksheet. In the worksheet, we used the “PivotTables” method and referenced the pivot table using the name given to the pivot table in the previous step. Then, we used the pivot table fields to mention the column we will work with from the data range.
Since we must work with multiple properties of the pivot table, we will use the WITH statement to avoid using the pivot sheet and pivot table names.
We can specify where to insert the pivot table field in the WITH statement. Since we must know the project value based on the project name, we will insert this into a row field.
Next, we must bring the “Projected_Credit_USD” to the values section to summarize the value based on the project name.
We can reference the “Projected_Credit_USD” column as follows.
Since we must insert the “Projected_Credit_USD” to the value field, the orientation will be “xlDataField.”
Now, let’s execute the code, and we will get the following pivot table, which shows project-wise value.
The pivot table summarized the values based on the project name available from the given data set. The VBA Pivot Table code is given below for your better understanding.
Sub VBA_Pivot_Table_Ex1()
'Define variables necessary for VBA Pivot Table creation
Dim PT As PivotTable
Dim PC As PivotCache
Dim PS As Worksheet
Dim DS As Worksheet
Dim LR As Long
Dim LC As Long
Dim DR As Range
'Set the data worksheet reference to variable
Set DS = Worksheets("Projects")
'Find the last used row (LR) and laste used column (LC) of the data range
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
'Set the data range using the LR and LC
Set DR = DS.Cells(1, 1).Resize(LR, LC)
On Error Resume Next
'Turn off alert message boxes
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Delete the pivot table to be inserted sheet before inserting one
Worksheets("Sales Summary Sheet").Delete
'Insert a new sheet
Worksheets.Add After:=Worksheets("Projects")
'Rename the sheet as "Sales Summary Sheet"
ActiveSheet.Name = "Sales Summary Sheet"
On Error GoTo 0
'Set the pivot table worksheet reference to variable
Set PS = Worksheets("Sales Summary Sheet")
'Set Pivot Cache
Set PC = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=DR)
'Create blank Pivot Table object
Set PT = PC.CreatePivotTable(TableDestination:=PS.Cells(1, 1), TableName:="Sales_Summary")
'Insert ProjectName to row field
With PS.PivotTables("Sales_Summary").PivotFields("ProjectName")
.Orientation = xlRowField
.Position = 1
End With
'Insert ProjectName to value section
With PS.PivotTables("Sales_Summary").PivotFields("Projected_Credit_USD")
.Orientation = xlDataField
.Position = 1
End WithEnd Sub
Example #2 – Create Row-Wise and Column-Wise Summarized Report
The more advanced level of the pivot table is to spread across rows and columns to get deep-dive analysis.
For example, to get the summary report based on “Priority” and “Review Status,” we must put either one into a row or column. The screenshot of the summary we will prepare using the VBA pivot table follows.
To do this, we will use the following three parts of the code.
In Part #1, we have a priority column, and it will be inserted into the “row” field (xlRowField) of the pivot table.
In Part #2, we have the review status column, which will be inserted into the “column” (xlXolumnField) field of the pivot table.
In Part #3, we have Projected_Credit_USD column, which will be inserted into the “value” (xlDataField) field of the pivot table.
Sub VBA_Pivot_Table_Ex2()
'Define variables necessary for VBA Pivot Table creation
Dim PT As PivotTable
Dim PC As PivotCache
Dim PS As Worksheet
Dim DS As Worksheet
Dim LR As Long
Dim LC As Long
Dim DR As Range
'Set the data worksheet reference to variable
Set DS = Worksheets("Projects")
'Find the last used row (LR) and last used column (LC) of the data range
LR = Cells(Rows.Count, 1).End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column
'Set the data range using the LR and LC
Set DR = DS.Cells(1, 1).Resize(LR, LC)
On Error Resume Next
'Turn off alert message boxes
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Delete the pivot table to be inserted sheet before inserting one
Worksheets("Sales Summary Sheet").Delete
'Insert a new sheet
Worksheets.Add After:=Worksheets("Projects")
'Rename the sheet as "Sales Summary Sheet"
ActiveSheet.Name = "Sales Summary Sheet"
On Error GoTo 0
'Set the pivot table worksheet reference to variable
Set PS = Worksheets("Sales Summary Sheet")
'Set Pivot Cache
Set PC = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=DR)
'Create blank Pivot Table object
Set PT = PC.CreatePivotTable(TableDestination:=PS.Cells(1, 1), TableName:="Sales_Summary")
'Insert Priority to row field
With PS.PivotTables("Sales_Summary").PivotFields("Priority")
.Orientation = xlRowField
.Position = 1
End With
'Insert Review Status to row field
With PS.PivotTables("Sales_Summary").PivotFields("Review_Status")
.Orientation = xlColumnField
.Position = 1
End With
'Insert ProjectName to value section
With PS.PivotTables("Sales_Summary").PivotFields("Projected_Credit_USD")
.Orientation = xlDataField
.Position = 1
End With
'Turn on display alerts
Application.DisplayAlerts = True
Application.ScreenUpdating = TrueEnd Sub
Once we run this code, we will get the following result.
We can use the other properties of the VBA Pivot Table and format them to look neat and beautiful.
Note: At the end of the code, turn display alerts to TRUE.
Important Things to Note
- VBA Pivot Table always creates a cache. Hence, we need to create that manually through code in VBA.
- Make sure to turn on all the turned-off alerts at the end of the code.
- VBA Pivot Table refresh happens through the pivot cache. Hence, we must use the following code.
PivotTables(“Sales_Summary”).PivotCache.Refresh
- VBA Pivot table filter can be set by using the orientation is equal to xlPageField
.Orientation = xlPageField
- VBA pivot table change data source can be done by changing the range variable reference.
- VBA Pivot Table need not necessarily to be created in a new sheet.
Frequently Asked Questions (FAQs)
If you have multiple pivot tables to be refreshed in one go, we can use the “Refresh All.” To use this as a VBA code, we can use the following code.ActiveWorkbook.RefreshAll
We can use the Subtotal property not to show subtotals. For example, we can use the following method to eliminate the subtotal for the “Priority” column.With ActiveSheet.PivotTables("Sales_Summary").PivotFields("Priority")
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
End With
To delete a VBA pivot table, we can use the following code.ActiveSheet.PivotTables("Sales_Summary").PivotSelect "", xlDataAndLabel, True
Selection.Delete
The pivot table in VBA won’t work for numerous reasons, and some of the common ones are listed below.
• If the given data worksheet name is different, then it won’t work.
• If the given column names for the fields are different, it will throw an error.
• If we try to give the same worksheet name, which already exists, then it will throw an error.
Download Template
This article must be helpful to understand the VBA Pivot Table, with its features and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Pivot Table. We learn how to create pivot table using VBA coding & summarize data using row & column fields, along with examples. You can learn more from the following articles –
Leave a Reply