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.

Note: Download the workbook to understand the data columns.

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.

Note: To have more different table styles record them macro and get the table styles.

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