How To Create A Pivot Table In Excel Vba

How to Create a Pivot Table in Excel VBA

Creating a pivot table in Excel VBA allows you to summarize and analyze data in a structured way. Here’s how you can do it:

Step 1: Set up your data

Before creating a pivot table, you need to have data in your Excel worksheet. Make sure the data is organized with clear headers and there are no blank rows or columns within the data set.

For example, suppose you have a sales dataset with columns like “Product”, “Region”, “Salesperson”, and “Revenue”.

Step 2: Define a PivotCache

In VBA, a PivotCache is used to store the data source for the pivot table. You can define a PivotCache by selecting the range of data you want to use as the source for the pivot table.

Dim ws As Worksheet
Dim pc As PivotCache

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set pc = ws.PivotTableWizard(SourceType:=xlDatabase, SourceData:=ws.Range("A1:D100"))

Step 3: Create a PivotTable

Once you have defined the PivotCache, you can create a PivotTable using the PivotTableWizard method. Specify the target range where you want to place the pivot table.

Dim pt As PivotTable
Dim ptRange As Range

Set ptRange = ws.Range("F1")
Set pt = pc.CreatePivotTable(TableDestination:=ptRange, TableName:="SalesPivotTable")

Step 4: Add fields to the PivotTable

After creating the PivotTable, you can add fields to it. Fields correspond to the columns in your data set, and you can choose which fields to include in the pivot table.

pt.PivotFields("Product").Orientation = xlRowField
pt.PivotFields("Region").Orientation = xlColumnField
pt.PivotFields("Revenue").Orientation = xlDataField

Step 5: Customize the PivotTable

You can further customize the PivotTable by applying formatting, adding filters, or changing the calculation settings.

' Apply number formatting to revenue field
pt.PivotFields("Revenue").NumberFormat = "#,##0.00"

' Add a filter to the region field
pt.PivotFields("Region").AutoSort xlAscending, "Region"

' Change the calculation setting to sum the revenue
pt.PivotFields("Revenue").Function = xlSum

Step 6: Refresh the PivotTable

If your data changes, you can refresh the PivotTable to update the results.

pt.RefreshTable

Conclusion

In this example, you learned how to create a pivot table in Excel VBA using the PivotTableWizard method. By defining a PivotCache, creating a PivotTable, and customizing it with fields and settings, you can easily summarize and analyze your data.

How To Create A Pivot Table In Excel Vba