Steps to Create a Pivot Table

Steps to Create a Pivot Table: Pivot Tables can be easily automated through VBA coding. To create a Pivot Table in VBA, follow the below step by step procedure to create your own Pivot Table. Here we have used dummy data to generate the reports.

1. Declare Pivot Cache Object

The first step is to create a pivot cache object to define the data source. It is enabled to ensure the fast functioning of the program. It holds the duplicate data, and whenever we make changes in the Pivot table, it uses the pivot cache. Hence, the actual data does not get hampered.

Program:

Sub CreatePivotTable()
 Dim PTCache As PivotCache 
 End Sub 

2. Declare the Pivot Table Object

The Pivot Table object is used to declare the variable as PivotTables. It contains all the objects of the Pivot Table in a common worksheet.

Program:

Sub CreatePivotTable()
                 Dim PTCache As PivotCache
 Dim PT As PivotTable 
 End Sub 

3. Set the Pivot Cache

We need to set the pivot cache to optimize the pivot table functioning. The user needs to refresh the data to reflect any changes in the Pivot table.

Program:

Sub CreatePivotTable()
     Dim PTCache As PivotCache
     Dim PT As PivotTable
     'Set the Pivot Cache
     Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheets("Pivot Table").Range("A1").CurrentRegion)
 End Sub 

4. Adding a new Worksheet

Adding a separate new worksheet to insert our Pivot Table as it would make it more readable and easier to relate with actual data.

Program:

Sub CreatePivotTable()
     Dim PTCache As PivotCache
     Dim PT As PivotTable
     'Set the Pivot Cache
     Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheets("Pivot Table").Range("A1").CurrentRegion)
     ' Adding a new sheet for Pivot Table
     Worksheets.Add
 End Sub 

5. Creating the Pivot Table

In the above worksheet, we will be inserting a blank new Pivot table where we can later define our columns, rows, and values.

Program:

 'Create the Pivot Table
 Set PT = ActiveSheet.PivotTables.Add( _
  PivotCache:=PTCache, _
 TableDestination:=Range("A3")) 

6. Specifying the Fields

Once we have added the blank Pivot Table, the next step is to insert the rows and columns and apply the required filter and features. Unlike in the below code, we have inserted “Region”, “Rep”, “OrderDate” and “Total Billing” Fields to our Table. Next, we PT object to auto group the Dates and have applied a filter to fetch the data associated with the “Central” field.

Program:

    'Specifying the Pivot Fields
     With PT
         .PivotFields("Region").Orientation = xlPageField
         .PivotFields("Rep").Orientation = xlRowField
         .PivotFields("OrderDate").Orientation = xlColumnField
         .PivotFields("Total Billing").Orientation = xlDataField
     'To Group the dates 
         .PivotFields("OrderDate").AutoGroup
     ' NO Field Caption
         .DisplayFieldCaptions = False
     'Filter Data for Central Region Only
         .PivotFields("Region").CurrentPage = "Central"
     'To Change the caption 
         .PivotFields("Sum of Total Billing").Caption = "Total Billing: "
     End With 

The Complete code along with the required Output.

Sub CreatePivotTable()
     Dim PTCache As PivotCache
     Dim PT As PivotTable
     'Set the Pivot Cache
     Set PTCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, Sheets("Pivot Table").Range("A1").CurrentRegion)
     ' Adding a new sheet for Pivot Table 
     Worksheets.Add
     'Create the Pivot Table
     Set PT = ActiveSheet.PivotTables.Add( _
         PivotCache:=PTCache, _
         TableDestination:=Range("A3"))
     'Specifying the Pivot Fields
     With PT 
         .PivotFields("Region").Orientation = xlPageField
         .PivotFields("Rep").Orientation = xlRowField
         .PivotFields("OrderDate").Orientation = xlColumnField
         .PivotFields("Total Billing").Orientation = xlDataField
     'To Group the dates
         .PivotFields("OrderDate").AutoGroup
     ' NO Field Caption 
         .DisplayFieldCaptions = False
     'Filter Data for Central Region Only
         .PivotFields("Region").CurrentPage = "Central"
     'To Change the caption
         .PivotFields("Sum of Total Billing").Caption = "Total Billing: "
     End With
 End Sub 

Output

In the below Output, we have created a Pivot Table based on the data. At the top, we have applied a filter on the Region field and have chosen “Central”. We have grouped them by months and have calculated the total billing for different months for Central. Hence, at last, have returned the grand total for all.