Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.
Before I hand over this guide to you about using VBA to create a pivot table, let me confess something.
I have learned using VBA just three years back. And, the first time when I had used a macro to create a pivot table, it was a failure.
Since then I have learned more from my bad coding rather than from the codes which actually work. That’s why I am trying to create an Ultimate VBA Guide (Small E-Book) to share right ways to learn VBA.
I have split the process of using VBA to create a pivot table in 8 steps. And, after following these steps you will able to automate your all the pivot tables just like below example.
Steps to create a pivot table using VBA.
Now, let’s understand each step to use to create a pivot table with VBA.
Before we start to create a pivot table with VBA, we have to declare variables that we can use in our code.
'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long
In above code, I have declared PSheet to create a sheet for a new pivot table, DSheet to use as a data sheet, PChache to use as a name for Pivot Table Cache, PTable for use as a name for our pivot table, PRange to define source data range & LastRow & LastCol to get the last row & column of our data range.
Before I create a new pivot table I have to insert a new worksheet for my Pivot Table.
Here is the code
'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")
Above code will first delete any old worksheet which has pivot table & then creates a new blank worksheet with the same name.
Now, next thing is to define data range which I want to use for my Pivot Table.
'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)
In above code, LastRow & LastCol will define last row & column of the data range. And then I have named that data range as PRange.
Doing this has a benefit that you don’t have to change data source every time while creating the pivot table.
Before you create a Pivot Table, you need to create pivot cache for that. In excel 2000 & above you have to create a pivot cache to define your data source.
'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")
Above code will, will create a pivot cache which I can use to create my pivot table.
Once you create a pivot cache, next step is to insert a pivot table.
And, the code is here.
'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable_ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")
By using above code, you can create a blank pivot table & use pivot cache as a data source.
Once you create a blank pivot table, we have to insert row & column fields in it.
'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With
I have used Year & Month as a row fields. And, zone as a column field. You can define positions of a field by using position numbers.
Now it’s time to insert a data field into the pivot table.
'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Position = 1 .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With
Use above code to insert a data field in your pivot table. I have used Amount as a data field but you can change it as per your need. You can also format numbers as per your need. And, If you are using more than one data fields you have to define their positions as well.
Now, it’s time to give a final touch of formatting.
'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"
By using above code, you can apply row strips and Pivot Style Medium 9. And, your pivot table is ready.
Just compiling all the above codes you can create a pivot table with a single click. Please download this entire code from here or you can download the sample file from link mention below.
By using this code you can automate your pivot tables. And, this is a one time set up and after that, you just need a click to create a pivot table.
Now tell me one thing. Have you ever used VBA to create a pivot table?
Please share your experience with me in the comment box. Also, share if you faced any problem while using this code.