Macro Codes Use VBA To Create Pivot Table In Excel

Before I hand over this guide to you about using VBA To Create 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 works. 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 creating a pivot table into following steps.

  • Declare Variables
  • Insert a New Worksheet
  • Define Data Range
  • Create Pivot Cache
  • Insert a Blank Pivot Table
  • Insert Row & Column Fields
  • Insert Data Fields
  • Format Pivot Table

Now, Let’s understand each step to use VBA to create a pivot table.

Declare Variables

Before we start to create a pivot table with VBA, we have to declare variables that we can use in our code.

Declare Variables To To Use VBA To Create Pivot Table In Excel

'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.

Insert a New Worksheet

Before I create a new pivot table I have to insert a new worksheet for my Pivot Table.Insert A New Worksheet To Use VBA To Create Pivot Table In Excel

Here is the code

'Delete Preivous Pivot Table Worksheet & Insert a New Blank Worksheet With Same Name
On Error Resume Next
Application.DisplayAlerts = False
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.

Define Data Range

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 I don’t have to change data source every time while creating the pivot table.

Create Pivot Cache

Before I create a Pivot Table, I 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), _ 

Above code will, will create a pivot cache which I can use to create my pivot table.

Insert a Blank Pivot Table

Once I create a pivot cache, next step is to insert a pivot table.

Insert A Blank PivotTo Use VBA To Create Pivot Table In Excel

And, the code is here.

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable_
(TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

By using above code, I can create a blank pivot table & use pivot cache as a data source.

Insert Row & Column Fields

Once you create a blank pivot table, we have to insert row & column fields in it.

Insert Row Column Fields Use VBA To Create Pivot Table In Excel

'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.

Insert Data

Now it’s time to insert a data field into the pivot table.

Insert Data Field To Use VBA To Create Pivot Table In Excel

'Insert Data Field
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount")
 .Orientation = xlDataField
 .Position = 1
 .Function = xlSum
 .NumberFormat = "#,##0"
 .Name = "Revenue "
End With

Use this above code to insert a data field in you 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.

Format Pivot Table

Now, it’s time to give a final touch of formatting.

Use VBA To Create Pivot Table In Excel Formatting

'Format Pivot Table
ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

By using above code, I have applied row strips & Pivot Style Medium 9.  And, my pivot table is ready.

By compiling all the above codes you can create a pivot table with a single click.

Please download this entire code from here or your can download the sample file from link mention below.

Download Sample File

download sample file to learn more about how to create a pivot table with vba



Use VBA code to create pivot table if you are getting bored by creating pivot tables again & again. This code can help you like a magic.

Do you ever create a pivot table by using a macro?

Please share your experience with me in the comment box.

Also, share if you faced any problem while using macros with a pivot table.

And, If you like this tip you can share it with your buddies on Facebook | Twitter | Google+ | LinkedIn

More Tips Related With Pivot Table