VBA Pivot Table

Pivot Tables are the heart of summarizing the report of a large amount of data. We can also automate creating a Pivot Table through VBA coding. They are an important part of any report or dashboard. It is easy to create tables with a button in Excel, but in VBA, we have to write some codes to automate our Pivot Table. Before Excel 2007 and its older versions, 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 is not easy, it is worth learning time. For example, we 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 we made many mistakes while attempting to create the Pivot Table.

But the actual thing is we have learned from my mistakes. So, we are writing this article to show you how to create Pivot Tables using code.

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

It is important to have data to create a Pivot Table. For this, we have created some dummy data. You can download the workbook to follow with the same data.

Step 1: Pivot Table is an object that references the Pivot Table and declares the variable as PivotTables.

Code:

Sub PivotTable()

  Dim PTable As PivotTable

End Sub

Step 2: Before creating a Pivot Table, we need to create a pivot cache to define the source of the data.

The Pivot Table creates a pivot cache in the background without troubling us in the regular worksheets. But in VBA, we have to create.

For this, define the variable a "PivotCache."

Code:

Dim PCache As PivotCache

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

Code:

Dim PRange As Range

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

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

Code:

Dim DSheet As Worksheet

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

Step 7: 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.

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

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

Step 10: Set the pivot range using the last row and last column.

It will set the data range perfectly. Furthermore, 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 PivotTable, we need to create a pivot cache. Set the pivot cache variable by using the below VBA code.

Step 12: Now, create a blank PivotTable.

Step 13: After inserting the PivotTable, we must insert the row field first. So, we will insert the row field as my 'Country" column.

Step 14: We will insert one more item into the row field as the second position item. We will insert the "Product" as the second line item to the row field.

Step 15: After inserting the columns into the row field, we need to insert values into the column field. We will insert the "Segment" into the column field.

Step 16: We need to insert numbers into the data field. So insert "Sales" into the data field.

Step 17: We have completed the Pivot Table summary part. Now, we need to format the table. To format the pivot table, use the below code.

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

We have completed it now. If we run this code using the F5 key or manually, we should get the PivotTable like this.

Like this, we can automate creating a PivotTable using VBA coding.

For your reference, we 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 Next
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

This article is a guide to the VBA Pivot Table. Here, we learn how to create a PivotTable in Excel using VBA code, a practical example, and a downloadable template. Below you can find some useful Excel VBA articles: -

• Filter in Pivot Table
• Pivot Table Calculated Field
• Multiple Sheets Pivot Table
• Slicer using Pivot Table
• VBA Web Scraping