Download Microsoft Excel VBA User Manual

Excel Visual Basic for Applications
To create a new PivotTable we can use the Add and CreatePivotTable methods of the
PivotCaches object:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R87C6").CreatePivotTable TableDestination:="", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
The SourceData is a range object containing the data for the report, the TableDestination
is where the report is returned. The TableName and other arguments are optional.
For example, define the source data as being all the data from A1 on the active
worksheet:
Set rngSource = ActiveSheet.Range("A1").CurrentRegion
The table destination is a new worksheet in the workbook, inserted after the active
sheet:
Set wksSales = Worksheets.Add(After:=ActiveSheet)
And create the PivotTable, naming it as 'Sales Report':
ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase,SourceData:=rngSource) _
.CreatePivotTable TableDestination:= wksSales.Range("A1"), _
TableName:="Sales Report"
Create an object variable to refer to the pivot table report:
Set ptSales = wksSales.PivotTables("Sales Report")
Now, add the fields required. Every column in the source data range creates a member of
the PivotFields collection as the Pivot cache contains all the source data. But to show a
field in the report you have to use the AddFields method:
ptSales.AddFields RowFields:="Country", ColumnFields:="Month"
Specifing them as:
PageFields:= "Product"
RowFields:= "Country"
ColumnFields:= "Month"
To specify two or more fields with the same orientation it is like this:
ColumnFields:= Array("Month","Country")
Data Fields
To add a data field to the report you do not use the AddFields method, rather you set the
Orientation property of an existing pivot table field to xlDataField (this field does not
have to be one of those already added, it can be any of the pivot fields):
ptSales.PivotFields("Units").Orientation = xlDataField
However, it is not possible to predict the name of the new pivot field as Excel names it
automatically depending on the default Summary function. If the default Summary
function is Sum then it is called "Sum of Units", if the default function is Count then it is
called "Count of Units". And, at this stage there is no way of finding out what the default
Summary function is! Once you have named the field Excel will not change it again
automatically but you need to make sure that you can specify the summary function
correctly.
Either, refer to the field not as a member of the PivotFields collection (where it is
contained but you do not know what it is called) but as a member of the DataFields
collection. As you create a data field it becomes the first member of this collection, the
next data field is the second member etc.
ptSales.PivotFields("Units").Orientation = xlDataField
ptSales.DataFields(1).Function = xlSum
ptSales.DataFields(1).Name = "Total Sales"
Page 58