When a pivot table is going to have more that one data field, you have a virtual field name called Data. Where you place the data field in the .AddFields method determines which view of the data you get.

The default setup, as shown in Figure 12.22, would have this AddFields line: PT.AddFields RowFields:=Array("Product", "Data")

The view shown in Figure 12.23 would use this code: PT.AddFields RowFields:=Array("Data", "Product")

The view that you need for this report would have Data as a column field: PT.AddFields RowFields:="Product", ColumnFields:="Data"

After adding a column field called Data, you would then go on to define four data fields:

1 Set up the data fields With PT.PivotFields("Quantity") .Orientation = xlDataField .Function = xlSum .Position = 1 .NumberFormat = "#,##0" End With

With PT.PivotFields("Revenue") .Orientation = xlDataField .Function = xlSum .Position = 2 .NumberFormat = "#,##0" End With

With PT.PivotFields("COGS")

.Orientation = xlDataField .Function = xlSum .Position = 3 .NumberFormat = "#,##0" End With

With PT.PivotFields("Profit") .Orientation = xlDataField .Function = xlSum .Position = 4 .NumberFormat = "#,##0" End With

0 0

Post a comment