How to Create Pivot Table in Excel through VBA?

Suppose we have a huge amount of data in Excel, Access, and SQL and we want to make summarize report from Pivot Table and also do analysis data so we use a pivot table in excel. We create a pivot table from other databases like MS Access, SQL Server Database.

how to create pivot table in excel using vba

Pivot Table

Pivot Table is a tool in excel which is used for quick summarize and analyze the data without any formula in excel sheet. There are many inbuilt features in pivot table which is help us to analyze and summarize data i.e Grouping, Functions, Filter.

Create a Pivot Table on Excel Data

When we create a pivot table on the excel data, the excel data’s field becomes for pivot table fields.

Pivot Table Options

  • Filter – It use for filtering on the pivot table.
  • Columns – It is a column heading for a pivot table.
  • Rows – It is a row heading for a pivot table
  • Values – It is used for calculation. We can choose different types of function i.e Sum, Average, Count, Min, Max.

 

Source Data

We can create a pivot table from a different data source i.e MS Access Database, SQL Server, and MS Excel itself.

 

What is the difference between a normal table and pivot table in excel

  • Normal Table is fixed but Pivot Table is flexible.
  • Normal Table show all entire data but Pivot Table show summarize data.

 

Rules for Creating Pivot Table

There are some rules for creating a pivot table.

  • The data should not have any blank column and a blank row. Pivot Table doesn’t select all data if there is blank row available.

how to create pivot table

 

  • Data must have a header because, without header, we get an error while creating Pivot Table as shown below picture.

how to create pivot table in excel 2016

 

How to Create Pivot Table in Excel through VBA?

Steps

(1). Declare Worksheet Object

            Dim ws As Worksheet

(2). Declare PivotCache Object

            Dim pc As PivotCache

(3). Declare PivotTable Object

            Dim pt As PivotTable

(4). Assign PivotCache Object as a Reference to an Object Variable using Set keyword

            Set pc = ThisWorkbook.PivotCaches.Create( _

            Excel is the source database

            SourceType:=xlDatabase, _

            Excel sheet is the source data

            SourceData:=Sheet1.Name & “!” &

            Starting range of data in excel sheet

            Sheet1.Range(“A1”).CurrentRegion.Address)

(5). Assign Worksheet Object as a Reference to an Object Variable using Set keyword

            Set ws = Worksheets.Add

(6). Rename new Worksheet

            ws.Name = “Pivot Table”

(7). Assign PivotTable Object as a Reference to an Object Variable using Set keyword

            Set pt = pc.CreatePivotTable( _

            We choose table destination as per our need

            TableDestination:=ws.Range(“A3”), _

            We assign the name of a pivot table

            TableName:=”AgentPivot”)

             We add fields for a pivot table

            pt.AddFields _

            We assign the table’s field as pivot table’s row heading

            RowFields:=”Agent”, _

            We assign the table’s field as pivot table’s column heading

            ColumnFields:=”AcctType”, _

            We assign the table’s field as pivot table’s filter

            PageFields:=”Branch”

            We add Data field for a pivot table. It is used for calculation.

            pt.AddDataField _

            We assign the table’s field as pivot table’s data field for calculation.

            Field:=pt.PivotFields(“Amount”), _

            We choose function for calculation in pivot table

            Function:=XlConsolidationFunction.xlSum

            We change format for number in pivot table

            pt.DataFields(1).NumberFormat = “0.00”

 

Pivot Cache

Pivot Cache is the memory for the Pivot Table and it is held in Excel’s memory. MS Excel takes a copy of the source data and stores it in the Pivot Cache while we creating a Pivot Table.

How to Create Pivot Table in Excel through VBA?

Here is full code for creating pivot table through VBA

Sub Create_Pivot()

    Dim ws As Worksheet

    Dim pc As PivotCache

    Dim pt As PivotTable

    Set pc = ThisWorkbook.PivotCaches.Create( _

        SourceType:=xlDatabase, _

        SourceData:=Sheet1.Name & “!” & Sheet1.Range(“A1”).CurrentRegion.Address)

    Set ws = Worksheets.Add

    ws.Name = “Pivot Table”

    Set pt = pc.CreatePivotTable( _

        TableDestination:=ws.Range(“A3”), _

        TableName:=”AgentPivot”)

    pt.AddFields _

        RowFields:=”Agent”, _

        ColumnFields:=”AcctType”, _

        PageFields:=”Branch”

    pt.AddDataField _

        Field:=pt.PivotFields(“Amount”), _

        Function:=XlConsolidationFunction.xlSum

    pt.DataFields(1).NumberFormat = “0”

End Sub

How to Create Pivot Table in Excel through VBA?

Download Workbook

Leave a Reply

Your email address will not be published. Required fields are marked *