INTRODUCTION
PIVOT TABLES are one of the most powerful features in Excel.
Pivot Tables are a very old feature of the Excel which kept on getting better and better with the newer versions.
Pivot Tables helps us to analyze large data within seconds as it requires no formulas, no dragging of functions etc.
Just select the column of your needs, put them at the right section and the table is ready. There are many functions which can be directly applied in the PIVOT TABLE.
We have already learnt to make use of PIVOT TABLES in the EXCEL BASICS.
If you want to revise kindly
CLICK HERE to learn how to use PIVOT TABLES IN EXCEL.
We’ll repeat the same process but now using VBA.
VBA will help us to automate our process and we can also take the help of pivot tables if needed.In this article we’ll learn how to create pivot tables and manipulate them using VBA with the help of examples.
CREATING FIRST PIVOT TABLE USING VBA
Let us create our first PIVOT TABLE using VBA.We have the following data.States with number of schools and colleges.
| COUNTRY | SCHOOLS | COLLEGES |
| UK | 19 | 66 |
| US | 17 | 37 |
| AUSTRALIA | 93 | 35 |
| FRANCE | 84 | 19 |
| SPAIN | 48 | 51 |
| UK | 12 | 64 |
| US | 22 | 24 |
| AUSTRALIA | 81 | 41 |
| UK | 62 | 89 |
| US | 77 | 80 |
| AUSTRALIA | 86 | 46 |
| FRANCE | 55 | 94 |
| UK | 64 | 97 |
| US | 71 | 54 |
| AUSTRALIA | 45 | 40 |
| FRANCE | 81 | 98 |
| UK | 66 | 17 |
| US | 95 | 83 |
| AUSTRALIA | 32 | 90 |
| FRANCE | 73 | 27 |
| SPAIN | 41 | 23 |
We can observe that there are many entries for a particular category.
For example, many countries have so many entries.
OBJECTIVE
CREATE A PIVOT TABLE USING THE GIVEN DATA
Make use of VBA to enter the given text into cell positioned at location D3.
PLANNING THE SOLUTION
We’ll use the standard procedure to create a PIVOT TABLE.After creating we’ll put COUNTRY in the ROW FIELDS and SCHOOLS AND COLLEGES in the DATAFIELDS.
CODE
'gyankosh.net
'creating a pivot table using VBA
Sub createsimplepivottable()
Dim tempCache As PivotCache
Dim table As PivotTable
Set tempCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("E5").CurrentRegion) 'Creating a cache for pivot table
Worksheets.Add 'Adding a new Sheet
Set table = ActiveSheet.PivotTables.Add( _
PivotCache:=tempCache, _
TableDestination:=Range("A3"))
With table 'Using with to avoid repeated writing of table
.PivotFields("COUNTRY").Orientation = xlRowField 'Setting the field in ROW FIELD
.PivotFields("COLLEGES").Orientation = xlDataField 'Setting the field in DATA(CALCULATION) FIELD
.PivotFields("SCHOOLS").Orientation = xlDataField 'Setting the field in DATA FIELD
End With
End Sub
EXPLANATION
We declare tempCache as PivotCache which is needed for the PIVOT TABLE CREATION and a pivot table variable as temptable.
After this we pick the range from the sheet using the statement
Set tempCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range(“E5”).CurrentRegion)
Source data is the region starting from the cell E5.
It means our data is present in E5.
Current region will consider all the contiguous data. The data which is connected with adjacent cells.
After this we add a new sheet.
There we take out the PIVOT TABLE and set table from the cache mentioning the cell as A3 i.e. the table should start from A3 .
After the table is ready, we’ll set the fields to the data which we put in pivot tables.
We put country in the row field and rest in data fields.
The output is shown below.
RUNNING THE CODE AND OUTPUT
STANDARD STRUCTURE OF CREATING A PIVOT TABLE USING VBA
BEFORE MOVING ON TO THE NEXT EXAMPLE, LET US UNDERSTAND THE BASIC STRUCTURE OF CREATING A PIVOT TABLE.
If we notice the last example, we can have a rough idea about the structure of the PIVOT TABLE in VBA.
Let us remember this structure so that we can finalize this.
Have a look at the structure below. This is the standard structure of the pivot table.
STRUCTURE OF THE PIVOT TABLE
STANDARD STEPS FOR A PIVOT TABLE CREATION
SUB PROCEDURE NAME
‘These two variables will be there always. Name can be as per your choice.
Dim tempCache As PivotCache
Dim table As PivotTable
Setting the table range to the Cache by passing the range.
Set tempCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range(“E5”).CurrentRegion)
‘Setting the pivot table at destination given by range.
Set table = ActiveSheet.PivotTables.Add( _
PivotCache:=tempCache, _
TableDestination:=Range(“A3”))
Setting up the the required fields
.PivotFields(“COUNTRY”).Orientation = xlRowField ‘Setting the field in ROW FIELD
End Sub
CREATING CUSTOM CALCULATION IN PIVOT TABLE USING VBA
OBJECTIVE
CREATING A PIVOT TABLE WITH A CUSTOM CALCULATION
DATA
We have a data about the daily rain in centimeters of a city.The data is random containing the details as the rainfall and month.We need to find the average rainfall in the city per month.The data is following.
| RAINFALL IN CITY A | |
| MONTH | RAINFALL |
| APRIL | 5.6 |
| MAY | 5.7 |
| JUNE | 6.8 |
| JULY | 9.6 |
| AUGUST | 9.7 |
| SEPTEMBER | 9.8 |
| OCTOBER | 8.1 |
| NOVEMBER | 5.4 |
| DECEMBER | 8.7 |
| OCTOBER | 8.2 |
| NOVEMBER | 7.3 |
| DECEMBER | 9.1 |
| JANUARY | 5.8 |
| JANUARY | 6.9 |
| FEBRUARY | 5 |
| NOVEMBER | 6.8 |
| DECEMBER | 9.9 |
| OCTOBER | 7.1 |
| NOVEMBER | 10 |
| DECEMBER | 7.4 |
| OCTOBER | 6.4 |
| NOVEMBER | 5.8 |
| DECEMBER | 5.3 |
CODE
'gyankosh.net
'FINDING OUT AVERAGE RAINFALL PER MONTH USING PIVOT TABLES IN VBA
Sub averageRainfall()
Dim tempCache As PivotCache
Dim table As PivotTable
Set tempCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("F5", "G28")) 'Creating a cache for pivot table
Worksheets.Add 'Adding a new Sheet
Set table = ActiveSheet.PivotTables.Add(PivotCache:= _
tempCache, TableDestination:=Range("f5"))
MsgBox ("I M AT 1")
With table 'Using WITH to avoid repeated writing of table
.PivotFields("MONTH").Orientation = xlRowField
.CalculatedFields.Add "remarks", "=RAINFALL"
.PivotFields("remarks").Orientation = xlDataField
.PivotFields("sum of remarks").Caption = "TOTAL RAINFALL"
.PivotFields("RAINFALL").Orientation = xlDataField
.PivotFields("sum of RAINFALL").Function = xlAverage
.PivotFields("average OF RAINFALL").Caption = "AVERAGE"
End With
End Sub
EXPLANATION
Let us check the explanation of introducing a custom field in pivot table using VBA.
We start with declaration of PIVOT CACHE and PIVOT TABLE.
We pass the values to the PIVOT CACHE with a range declared by F5:G28 which is the range of the data.
We add a new sheet.
We pass the output range to the PIVOT TABLE and create it from the CACHE .
The output range is set as F5. But this F5 will be of the new sheet which we just created. Now the table is created and we need to fix its fields into different options like row or column or data. We put the MONTHS in row field. We add a temporary custom field named remarks in which we pass the values of RAINFALL.
We put this as data field which gives us the monthly sum of the rainfall.
NOTICE THE NAMES USED.
Pivot table immediately changes the name and we need to refer to those field with the changed name.
We again change the caption to the one we need. After this, we again put the field RAINFALL and change its function to average .
We again change its caption. The following picture shows the running process.
CUSTOM FIELD IS USED AS FOLLOWS:
table.calculatedfields.Add “name of the field” “formula”
formula can be anything connected with the field names.
RUNNING THE CODE AND OUTPUT
