EDUCBA

Home VBA VBA Resources VBA Tips VBA Pivot Table
 

VBA Pivot Table

Madhuri Thakur
Article byMadhuri Thakur

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

Recommended Articles

This is a guide to VBA Pivot Table. Here we discuss how to create a pivot table using VBA codes in excel along with an example and downloadable excel template. You may also look at the following articles to learn more –

  1. VBA PowerPoint
  2. VBA Refresh Pivot Table
  3. VBA On Error Resume Next
  4. VBA ScreenUpdating