How To Create Pivot Table In Vba Macro

VBA Pivot Table

Excel VBA Pivot Table

VBA Pivot Table helps you to summarize reports from a large data set. Pivot can consider as a tiny form of the entire data set. A quick view of large data set is possible through a pivot table. A pivot table is an easy way to filter data accordingly. From the available data, you can highlight data the way you want. A pivot table allows you to combine the huge data analyze the data, and produce reports which meet your business requirements.

Excel offers a built-in pivot table which is easy to create by applying a pivot table over a datasheet. It is possible to generate a report automatically in excel once the data is supplied. VBA codes will help you to create an automatic pivot table.

How to Create a Pivot Table Using Excel VBA?

The common steps to insert a pivot table are first inserting a pivot table from the Insert menu, then selecting the tables you want to change into a pivot table. The selected table will become the source data, and the pivot table will be created accordingly.

To build a pivot report, you have to select the fields to the filters, values, etc. In a similar way, these steps can be automated using the VBA codes. We need an excel sheet of data to operate with a pivot table. Let's learn how to create a pivot table through the Excel VBA codes.

You can download this VBA Pivot Table Excel Template here – VBA Pivot Table Excel Template

Step1: Pivot table should consider as a reference object. Create an object for the pivot table; this object will be used to point to the pivot table on further codes. Name the variable as pvtable. Declare a function and pivot table object.

Code:

              Sub              PivotTable()                              Dim              pvtable              As              PivotTable              End Sub

VBA Pivot Table Example 1-1

Step 2: To keep the data files, we need a pivot cache to declare the source of the data. Before creating a pivot table, declare a pivot cash variable. Declare the variable pvcache for pivot cache.

Code:

              Dim              pvcache              As              PivotCache

VBA Pivot Table Example 1-2

Step 3: The data is within the worksheet and should specify a range to hit the cell that you need. In the pivot table, the source data is spread as rows and columns so to point to a particular range; we need a variable. Define pvrange as a range variable.

Code:

              Dim              pvrange              As              Range

VBA Pivot Table Example 1-3

Step 4: A worksheet needs to insert the pivot table which you need to create. Declare a variable as a worksheet. Define pvsheet as a worksheet.

Code:

              Dim              pvsheet              As              Worksheet

VBA Pivot Table Example 1-4

Step 5: You need a similar variable to use the datasheet, which should contain the data you want to plot as a pivot table. So the datasheet variable is declared as pdsheet.

Code:

              Dim              pdsheet              As              Worksheet

VBA Pivot Table Example 1-5

Step 6: You need two more variables as long datatype to indicate the last used row and column for pivot table creation. This can be any row or column, so there are chances to be a number of rows and columns beyond the integer data type limit. Let's name it plr and plc.

Code:

              Dim              plr              As Long              Dim              plc              As Long

VBA Pivot Table Example 1-6

Step 7: The next step is to delete if there is any pivot table already created. This will help to avoid confusions on which table the source data to be plotted. To delete the previous pivot table sheet and create a new sheet to insert the pivot table.

Code:

              On Error Resume Next              Application.DisplayAlerts =              False              Application.ScreenUpdating =              False              Worksheets("pvsheet").Delete                'to delete the exisiting pivot table in the worksheet              Worksheets.Add After:=ActiveSheet              ' to add a new worksheet              ActiveSheet.Name = "pvsheet"              ' to rename the worksheet into "pvsheet"              On Error GoTo 0

VBA Pivot Table Example 1-7

Step 8: Assign an object variable for the pivoting sheet and data sheet respectively to the variables pvsheet and pdsheet. This will be used further for specifying the worksheets.

Code:

              Set              pvsheet = Worksheets("pvsheet")              Set              pdsheet = Worksheets("pdsheet")

VBA Pivot Table Example 1-8

Step 9: Once the worksheets are set, the next item is we need the last used row and column for creating a pivot report. Find the last used row and column using the declared variables plr and plc.

Code:

              'two variable to find Last used row and column in pdsheet              plr = pdsheet.Cells(Rows.Count, 1).End(xlUp).Row plc = pdsheet.Cells(1, Columns.Count).End(xlToLeft).Column

VBA Pivot Table Example 1-9

Step 10: Like we mentioned previously, the range of cells specifies the data in the worksheet. You have to set the pivot range in the next step. It is already declared as a variable to use the pivot range "pvrange".

Code:

              'initializing pivot table data range              Set              pvrange = pdsheet.Cells(1, 1).Resize(plr, plc)

VBA Pivot Table Example 1-10

Since the range is set using resize property of cell ranges, it will resize the pvrange will adjust the pivot range accordingly. So the pvrange will adjust is there any addition or deletion of the rows or column happen.

Step 11: It's time to set the pivot cache, which is the pivot table's source. Use the object pvcache to set the source cache.

Code:

              'pivot cahe              Set              pvcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pvrange)

Set the pivot cache

Step 12:Here, the Sales report for different products will be converted into a pivot table through this. Create a pivot table as blank to which you can add the data set further.

Code:

              'new blank pivot table              Set              pvtable = pvcache.CreatePivotTable(TableDestination:=pvsheet.Cells(1, 1), TableName:="Sales_Report")

VBA Pivot Table Example 1-12

Step 13: Once the pivot is inserted, you have to specify the different fields you want to insert to the pivot table. So insert the first-row field. Here the first row starts with the product.

Code:

              'Insert Product to Row Filed              With              pvsheet.PivotTables("Sales_Report").PivotFields("Product")   .Orientation = xlRowField   .Position = 1              End With

Insert the Product field

Step 14: The next is to specify the second field you want to insert to the pivot table. In the same way, insert the second-row field street to the pivot table.

Code:

              'Insert Street to Row Filed & position 2              With              pvsheet.PivotTables("Sales_Report").PivotFields("Street")   .Orientation = xlRowField   .Position = 2              End With

Insert the street field

Step 15: Insert the next field to the pivot table, and the next is a town. Give the code to insert the town field.

Code:

              'Insert town to Column Filed              With              pvsheet.PivotTables("Sales_Report").PivotFields("Town")   .Orientation = xlColumnField   .Position = 1              End With

Insert the town field

Step 16: Till this, the data inserted were text type. Now we need to insert the number of sales to the pivot table.

Code:

              'Insert Sales column to the data field              With              pvsheet.PivotTables("Sales_Report").PivotFields("Sales")   .Orientation = xlDataField   .Position = 1              End With

Number of sales

Step 17: You have inserted the fields that need to create a pivot table. And the pivot table is almost finished now; you can set the format of the pivot table. This will specify the type of table through table style. Row Axis Layout is also set in the way you want.

Code:

              'set the format Pivot Table              pvsheet.PivotTables("Sales_Report").ShowTableStyleRowStripes =              True              pvsheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14"

VBA Pivot Table Example 1-17

Step 18:To show the row filed values items in tabular form, add the below code at the bottom.

Code:

              'to show the pivot table in Tabular form              pvsheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow  Application.DisplayAlerts =              True              Application.ScreenUpdating =              True

Row filed values items

Step 19:Press the run button or hit F5 to run the code. This will produce a pivot table from the data source sheet. The data given in rows and columns will be changed into the pivot table. The pivot table will be visible on the pivot table worksheet.

Check the output, and you can see the data source is converted into the pivot table as below; the mentioned columns are converted into the filter forms.

VBA Pivot Table Example 1-19

Pivot fields are visible on the right side. You can make changes according to your need for how the data needs to showcase.

For your reference, I have given the code below.

Code:

              Sub              PivotTable()                              Dim              pvtable              As              PivotTable                              Dim              pvcache              As              PivotCache                              Dim              pvrange              As              Range                              Dim              pvsheet              As              Worksheet                              Dim              pdsheet              As              Worksheet                              Dim              plr              As Long                              Dim              plc              As Long                              On Error Resume Next              Application.DisplayAlerts =              False              Application.ScreenUpdating =              False              Worksheets("pvsheet").Delete              'to delete the exisiting pivot table in the worksheet              Worksheets.Add After:=ActiveSheet              ' to add a new worksheet              ActiveSheet.Name = "pvsheet"              ' to rename the worksheet into "pvsheet"                              On Error GoTo 0                              Set              pvsheet = Worksheets("pvsheet")                              Set              pdsheet = Worksheets("pdsheet")                              'two variable to find Last used row and column in pdsheet              plr = pdsheet.Cells(Rows.Count, 1).End(xlUp).Row   plc = pdsheet.Cells(1, Columns.Count).End(xlToLeft).Column                              'initializing pivot table data range                              Set              pvrange = pdsheet.Cells(1, 1).Resize(plr, plc)                              'pivot cahe                              Set              pvcache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pvrange)                              'new blank pivot table                              Set              pvtable = pvcache.CreatePivotTable(TableDestination:=pvsheet.Cells(1, 1), TableName:="Sales_Report")                              'Insert Product to Row Filed                              With              pvsheet.PivotTables("Sales_Report").PivotFields("Product")     .Orientation = xlRowField     .Position = 1                              End With                              'Insert Street to Row Filed & position 2                              With              pvsheet.PivotTables("Sales_Report").PivotFields("Street")     .Orientation = xlRowField     .Position = 2                              End With                              'Insert town to Column Filed                              With              pvsheet.PivotTables("Sales_Report").PivotFields("Town")     .Orientation = xlColumnField     .Position = 1                              End With                              'Insert Sales column to the data field                              With              pvsheet.PivotTables("Sales_Report").PivotFields("Sales")     .Orientation = xlDataField     .Position = 1                              End With                              'set the format Pivot Table              pvsheet.PivotTables("Sales_Report").ShowTableStyleRowStripes =              True              pvsheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14"                              'to show the pivot table in Tabular form              pvsheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow    Application.DisplayAlerts =              True              Application.ScreenUpdating =              True              End Sub

Things to Remember

Specify the source sheet where the data is to supply for a pivot table.
Starting row and column should be mentioned properly.
Use the necessary objects to pinpoint the pivot cache, range, starting and ending of rows.
Arrange the data source with the proper format since this is the automation process.