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.
Steps to Create Pivot Table in VBA
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.
Sub PivotTable() Dim PTable As PivotTable End Sub
Step 2: Before we create a pivot table first we need to create a pivot cache to define the source of the data.
In regular worksheet 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.
Dim PCache As PivotCache
Step 3: To determine the pivot data range define the variable as a range.
Dim PRange As Range
Step 4: To insert a pivot table we need a separate sheet to add worksheet for pivot table declare the variable as a worksheet.
Dim PSheet As Worksheet
Step 5: Similarly to reference the data containing worksheet declare one more variable as Worksheet.
Dim DSheet As Worksheet
Step 6: Finally to find the last used row & column define two more variables as Long.
Dim LR As Long Dim LC As Long
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.
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: Now set the pivot range by using the last row & last column.
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 code.
Step 12: Now create a blank pivot table.
Step 13: After inserting the pivot table we need to insert row field first. So I will insert the row field as my Country column.
Step 14: Now one more item I will insert to row field as the second position item. I will insert the Product as the second line item to the row field.
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.
Step 16: Now we need to insert numbers to the data field. So insert “Sales” to the data field.
Step 17: We are done with pivot table summary part, now we need to format the table. To format pivot table use below code.
To show the row filed values items in tabular form add the below code at the bottom.
Ok, we are done if we run this code using F5 key or manually then, we should get the pivot table like this.
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 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 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 –