
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
PIVOT TABLES USING VBA
INTRODUCTION
CREATING FIRST PIVOT TABLE USING VBA
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.
Now if we want to sum the number of colleges and schools in each country, we’ll need to sum all the entries. But with the help of PIVOT TABLE, we can do that in seconds. So let us create the table using VBA.
OBJECTIVE
Make use of VBA to enter the given text into cell positioned at location D3.
PLANNING THE SOLUTION
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 fromt he 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
gyankosh.net
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
DATA
RAINFALL IN CITY A | |
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”))
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
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.
RUNNING THE CODE AND OUTPUT
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.