VBA Pivot Table

Excel VBA Pivot Table

Pivot Tables are the heart of summarizing the report of a large amount of data. We can also automate the process of creating a pivot table through VBA coding. They are important part of any report or dashboard, in excel it is easy to create tables with a button but in VBA we have to write some codes to automate our pivot table, before excel 2007 and its older versions in VBA we did not need to create a cache for pivot tables but in excel 2010 and its newer versions caches are required.

VBA can save tons of time for us in our workplace. Even though mastering it isn’t that easy but worth spending time to learn this. I took 6 months to understand the process of creating pivot tables through VBA. You know what those 6 months have done wonders for me because I made so many mistakes while attempting to create the pivot table.

But the actual thing is I have learned from my mistakes, and now I am writing this article to show you how to create pivot tables using code.

With just a click of a button, we can create reports.

VBA Pivot Table

Steps to Create Pivot Table in VBA

You can download this VBA Pivot Table Template here – VBA Pivot Table Template

To create a pivot table, it is important to have data. For this, I have created some dummy data. You can download the workbook to follow with me with the same data.

Step 1: Pivot Table is an object to reference the pivot table declare the variable as PivotTables.

Code:

Sub PivotTable()

  Dim PTable As PivotTable

End Sub
VBA Pivot Table Example 1

Step 2: Before we create a pivot table first, we need to create a pivot cache to define the source of the data.

In the regular worksheets, the pivot table without troubling us will create a pivot cache in the background. But in VBA, we have to create.

For this, define the variable a PivotCache.

Code:

Dim PCache As PivotCache
VBA Pivot Table Example 1-1

Step 3: To determine the pivot data range define the variable as a range.

Code:

Dim PRange As Range
VBA Pivot Table Example 1-2

Step 4: To insert a pivot table, we need a separate sheet to add worksheets for the pivot table to declare the variable as a worksheet.

Code:

Dim PSheet As Worksheet
VBA Pivot Table Example 1-3

Step 5: Similarly, to reference the data containing worksheet declare one more variable as Worksheet.

Code:

Dim DSheet As Worksheet
VBA Pivot Table Example 1-4

Step 6: Finally, to find the last used row & column, define two more variables as Long.

Code:

Dim LR As Long
Dim LC As Long
VBA Pivot Table Example 1-5

Step 7: Now, we need to insert a new sheet to create a pivot table. Before that, if any pivot sheet is there, then we need to delete that.

VBA Pivot Table Example 1-6

Step 8: Now, set the object variable PSheet and DSheet to Pivot Sheet and Data Sheet, respectively.

VBA PivotTable Example 1-7

Step 9: Find the last used row and last used column in the datasheet.

VBA Pivot Table Example 1-8

Step 10: Now set the pivot range by using the last row & last column.

VBA Pivot Table Example 1-9

This will set the data range perfectly. It will automatically select the data range, even if there is any addition or deletion of data in the datasheet.

Step 11: Before we create a pivot table, we need to create a pivot cache. Set the pivot cache variable by using the below VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

VBA Pivot Table Example 1-10

Step 12: Now, create a blank pivot table.

VBA Pivot Table Example 1-11

Step 13: After inserting the pivot table, we need to insert the row field first. So I will insert the row field as my Country column.

Note: Download the workbook to understand the data columns.
VBA PivotTable Example 1-12

Step 14: Now, one more item I will insert into the row field as the second position item. I will insert the Product as the second line item to the row field.

VBA PivotTable Example 1-13

Step 15: After inserting the columns to the row field, we need to insert values to the column field. I will insert the “Segment” to the column field.

VBA PivotTable Example 1-14

Step 16: Now, we need to insert numbers into the data field. So insert “Sales” to the data field.

VBA PivotTable Example 1-15

Step 17: We are done with the pivot table summary part. Now we need to format the table. To format the pivot table, use the below code.

VBA PivotTable Example 1-16
Note: To have more different table styles record them macro and get the table styles.

To show the row filed values items in tabular form, add the below code at the bottom.

VBA PivotTable Example 1-17

Ok, we are done if we run this code using the F5 key or manually then, we should get the pivot table like this.

VBA Pivot Table Example 1-18

Like this, using VBA coding, we can automate the process of creating a pivot table.

For your reference, I have given the code below.

Sub PivotTable()

Dim PTable As PivotTable
Dim PCache As PivotCache
Dim PRange As Range
Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim LR As Long
Dim LC As Long

On Error Resume NextError Resume NextVBA On Error Resume Statement is an error-handling aspect used for ignoring the code line because of which the error occurred and continuing with the next line right after the code line with the error.read more
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet
Worksheets.Add After:=ActiveSheet ' This will add new worksheet
ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet"
On Error GoTo 0

Set PSheet = Worksheets("Pivot Sheet")
Set DSheet = Worksheets("Data Sheet")

'Find Last used row and column in data sheet
LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Set the pivot table data range
Set PRange = DSheet.Cells(1, 1).Resize(LR, LC)

'Set pivot cahe
Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange)

'Create blank pivot table
Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report")

'Insert country to Row Filed
With PSheet.PivotTables("Sales_Report").PivotFields("Country")
.Orientation = xlRowField
.Position = 1
End With

'Insert Product to Row Filed & position 2
With PSheet.PivotTables("Sales_Report").PivotFields("Product")
.Orientation = xlRowField
.Position = 2
End With

'Insert Segment to Column Filed & position 1
With PSheet.PivotTables("Sales_Report").PivotFields("Segment")
.Orientation = xlColumnField
.Position = 1
End With

'Insert Sales column to the data field
With PSheet.PivotTables("Sales_Report").PivotFields("Sales")
.Orientation = xlDataField
.Position = 1
End With

'Format Pivot Table
PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True
PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14"

'Show in Tabular form
PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Recommended Articles

This has been a guide to VBA Pivot Table. Here we learn how to create a pivot table in excel using VBA code along with a practical example and a downloadable template. Below you can find some useful excel VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>