A Step-by-Step Guide to Create a Pivot Table in Excel using VBA – MACRO CODE

The first step is to declare the variables we need to use in our code to define different things.

'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

Here’s what each line does:

• Dim PSheet As Worksheet: This line declares a Workbook object variable named PSheet, representing a worksheet where the pivot table will be created.
• Dim DSheet As Worksheet: This line declares another Workbook object variable named DSheet, which will represent the worksheet that contains the source data.
• Dim PCache As PivotCache: This line declares a PivotCache object variable named PCach, used to store the data for the pivot table.
• Dim PTable As PivotTable: This line declares a PivotTable object variable namedPTable`, which will represent the pivot table itself.
• Dim PRange As Range: This line declares a Range object variable named PRange, representing the data range the pivot table will analyze.
• Dim LastRow As Long: This line declares a Long variable named LastRow, which will store the row number of the last row in the data set.
• Dim LastCol As Long: This line declares another Long variable named LastCol, which will be used to store the column number of the last column in the data set.

Before creating a pivot table, Excel inserts a blank sheet and then creates a new pivot table there.

And, the below code will do the same for you.

'Declare Variables
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")

• On Error Resume Next: This line prevents the code from stopping if it encounters an error. Instead, it ignores the error and moves to the next line of code.
• Application.DisplayAlerts = False: This line turns off alerts and warnings that Excel might show while running the code.
• Worksheets(“PivotTable”).Delete: This line deletes the worksheet named “PivotTable” if it exists already.
• Sheets.Add Before:=ActiveSheet: This line adds a new sheet before the current active sheet.
• ActiveSheet.Name = “PivotTable”: This line renames the newly created sheet to “PivotTable”.
• Application.DisplayAlerts = True: This line turns the alerts back on after the operations are done.
• Set PSheet = Worksheets(“PivotTable”): This line creates a reference to the “PivotTable” worksheet and assigns this reference to the variable PSheet.
• Set DSheet = Worksheets(“Data”): This line creates a reference to the “Data” worksheet and assigns this reference to the variable DSheet.

After inserting a new worksheet, this code will set the value of the PSheet variable to the pivot table worksheet and DSheet to the source data worksheet.

Note – Make sure to change the name of the worksheets in the code to the names that you have in your data.

Now, the next thing is to define the data range from the source worksheet. Here, you need to take care of one thing: you can’t specify a fixed source range.

You need a code that can identify the entire data from the source sheet. Below is the code:

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

• LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row: This line finds the last row of the first column (column 1) with data. Rows.Count gives the total number of rows in the worksheet, and End(xlUp) moves up from the bottom until it finds a cell with data.
• LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column: This line finds the last column of the first row (row 1) with data. Columns.Count gives the total number of columns in the worksheet, and End(xlToLeft) moves to the left from the far right of the worksheet until it finds a cell with data.
• Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol): This line sets a range that starts from the cell in the first row and first column (Cell 1, 1) and resizes it to include all cells until the last row with data and the last column with data. PRange now refers to the range of cells that contains data in the worksheet.

Note – You don’t need to change the data source every time while creating the pivot table.

In Excel 2000 and above, before creating a pivot table, you need to create a pivot cache to define the data source.

Normally, when you create a pivot table, Excel automatically creates a pivot cache without asking you, but when you need to use VBA, you need to write code for this.

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=PRange)

• TableName:=”SalesPivotTable”: This parameter in the CreatePivotTable method sets the name of the pivot table being created. In this case, the pivot table will be named “SalesPivotTable”.
• Set PCache = ActiveWorkbook.PivotCaches.Create: This line of code is initializing the creation of a cache for the pivot table. A pivot cache is a particular type of data structure that Excel uses to store a snapshot of the source data for a pivot table. The ActiveWorkbook object refers to the active workbook in the Excel application.
• SourceType:=xlDatabase: This line in the Create method specifies the type of data source used for the pivot table. The xlDatabase value indicates that the source data is coming from a database, which in Excel could be a range of cells in a spreadsheet structured like a database.
• SourceData:=PRange: This line in the Create method specifies the data used for the pivot table. In this case, the PRange refers to a named range in the workbook that contains the source data.
• .CreatePivotTable: This method is called on the pivot cache object (PCache) and creates the pivot table from the data stored in it.
• TableDestination:=PSheet.Cells(2, 2): This parameter in the CreatePivotTable method specifies where the upper-left cell of the pivot table will be added in the worksheet. The PSheet.Cells(2,2) value represents the cell in the second row and second column of the sheet referred to by PSheet.

After the pivot cache, the next step is to insert a blank pivot table.

Just remember when you create a pivot table, what happens, you always get a blank pivot first, and then you define all the values, columns, and rows.

This code will do the same:

'Insert Pivot Table
Set PTable = PCache.CreatePivotTable( _
TableDestination:=PSheet.Cells(2, 2), TableName:="SalesPivotTable")

This code creates a blank pivot table and names it “SalesPivotTable”. You can change this name from the code itself.

After creating a blank pivot table, the next thing is to insert row and column fields, just like you do normally.

For each row and column field, you need to write a code.

Here we want to add years and months in the row field and zones in the column field.

Here is the code:

'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

In this code, you have mentioned year and month as two fields. Now, if you look at the code, you’ll find that a position number is also there.

This position number defines the sequence of fields.

Whenever you need to add more than one field (Row or Column), specify their position.

You can change fields by editing their name from the code.

The main thing is to define the value field in your pivot table.

The code for defining values differs from defining rows and columns because we must define the formatting of numbers, positions, and functions here.

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

You can add the amount as the value field with the above code. And this code will format values as a number with a (,) separator.

We use xlsum to sum values, but you can also use xlcount and other functions.

You need to use a code to format your pivot table. There is a default formatting in a pivot table, but you can change that formatting.

With VBA, you can define formatting styles within the code.

Code is:

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

The above code will apply row strips and the “Pivot Style Medium 9” style, but you can also use another style from this link.