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.

Steps to Create a Pivot Table

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:

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:

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:

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:

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:

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:

The Complete code along with the required Output.

Steps to Create a Pivot Table

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.

Steps to Create a Pivot Table

Pin It on Pinterest

Share This