EXCEL VBA SERIES – POST – 14 – [ Auto creation of Pivot table with required filters , rowFields and columnFields ]

EXCEL VBA SERIES – POST – 14 – [ Auto creation of Pivot table with required filters , rowFields and columnFields ]
Spread the love

 

EXCEL VBA SERIES – POST – 14 – [ Auto creation of Pivot table with required filters , rowFields and columnFields ]:

In this blog post, we will see how to auto create a pivot table​​ using excel vba , we will specify the filters, rowfields and columnfields during pivot creation. ​​ We will also see how to add custom columfields and add formatting of numbers.​​ 

Let’s take the below sample data

 

Using this data, we will do the following,

  • Keep Dept , Region and customer as filters

  • Display cost types as rowfields

  • Display month as column field , display sum of revenue and cost for each month.​​ 

  • Display Total sum of revenue and Total sum of cost

  • Display the numbers in proper dollar format.​​ 

Month

Dept

Region

Customer

Revenue

Cost Types

Cost

Jan

Admin

East

ABC

5000

Electricity

300

Jan

IT

West

DEF

4000

Plumbing

500

Feb

HR

North

GHI

2000

Furniture

600

Feb

Finance

South

JKL

6000

Electronics

200

Mar

Admin

East

ABC

5000

Concierge

700

Mar

IT

West

DEF

4000

Other Costs

500

Mar

HR

North

GHI

2000

Plumbing

600

Apr

Finance

South

JKL

6000

Furniture

200

May

Finance

South

JKL

7000

Electronics

700

 

We will create a new sheet called “Pivot-Table” to output the pivot table creation. I have specified the comments in-line with the code which will explain the code.​​ 

Below is the code for your reference.

Sub createPivot()

 

'Error handler to delete the pivot table sheet in case

'there is disruption in pivot table creation and also to

'display the error message

On Error GoTo ErrHandler:

 

'Get the last row of the table

With ThisWorkbook.Sheets("Pivot")

  lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

End With

 

'Get the last column of the table

With ThisWorkbook.Sheets("Pivot")

  LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column

End With

 

'Specify the pivot range

With ThisWorkbook

Set pRange = .PivotCaches.Create(xlDatabase, "Pivot!R1C1:R" & lastrow & "C" & LastCol)

End With

    

'Add a new sheet to output the pivot table creation

Set ws1 = ThisWorkbook.Worksheets.Add

ws1.Name = "Pivot-Table"

 

'Create the pivottable

Set pTable = pRange.CreatePivotTable(ws1.Range("A1"))

 

 

'Update filters, column and rowfields

With pTable

    With .PivotFields("Dept")

        .Orientation = xlPageField

        .Position = 1

        .LayoutForm = xlTabular

    End With

    

    With .PivotFields("Region")

        .Orientation = xlPageField

        .Position = 1

        .LayoutForm = xlTabular

    End With

    

    With .PivotFields("Customer")

        .Orientation = xlPageField

        .Position = 1

        .LayoutForm = xlTabular

    End With

    

    With .PivotFields("Cost Types")

        .Orientation = xlRowField

        .Position = 1

        .LayoutForm = xlTabular

    End With

    

    With .PivotFields("Month")

        .Orientation = xlColumnField

        .Position = 1

        .LayoutForm = xlTabular

    End With

    

    'Custom column creation

    .AddDataField .PivotFields("Revenue"), "Sum of Revenue", xlSum

    .AddDataField .PivotFields("Cost"), "Sum of Cost", xlSum

    

    'formating numbers

    With .PivotFields("Sum of Revenue")

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    End With

    

    With .PivotFields("Sum of Cost")

        .NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""??_);_(@_)"

    End With

 

End With

 

'Displaying the cost types in a particular order

 

With pTable

    .PivotFields("Cost Types").PivotItems("Electricity").Position = 1

    .PivotFields("Cost Types").PivotItems("Plumbing").Position = 2

    .PivotFields("Cost Types").PivotItems("Furniture").Position = 3

    .PivotFields("Cost Types").PivotItems("Electronics").Position = 4

    .PivotFields("Cost Types").PivotItems("Concierge").Position = 5

    .PivotFields("Cost Types").PivotItems("Other Costs").Position = 6

    'you can also hide certain rowfields if you want

    '.PivotFields("Cost Types").PivotItems("Other Costs").Visible = False

End With

 

 

'Error handler

ErrHandler:

If Err.Description <> "" Then

    ThisWorkbook.Sheets("Pivot-Table").Delete

    MsgBox Err.Description

End If

 

 

End Sub

 

The final output will be like this.

Thanks for viewing the post, please post your comments​​ below,

The exported module is also available under my github page​​ 

https://github.com/rajathithan/excel-vba/

Please Like and Follow us on facebook and twitter

https://www.facebook.com/gadoth/

https://www.twitter.com/GadothDA



Download

Related posts

Leave a Comment