I am creating a new Pivot Table and everything works except when I add the second and third Data Field. While each Data Field shows up under Values on the Pivot Table, the second and third field overwrites the first field in the fifth column.
How do I get the second Data Field to show-up in the sixth column and the third Data Field to show-up in the seventh column?
How do I get the second Data Field to show-up in the sixth column and the third Data Field to show-up in the seventh column?
Code:
Option Explicit
Sub CreatePivotTable()
Dim objTable As Object
Dim objField As Object
Dim DataSheet As Worksheet
Set DataSheet = Sheets("Combined Data")
DataSheet.Select
DataSheet.Range("A1").Select
Set objTable = DataSheet.PivotTableWizard
Set objField = objTable.PivotFields("BU")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Material")
objField.Orientation = xlRowField
'objField.Orientation = xlColumnField
Set objField = objTable.PivotFields("Updated Descritpion")
objField.Orientation = xlRowField
objField.Caption = "Material Descritpion"
'objField.Orientation = xlDataField
Set objField = objTable.PivotFields("Tactical Plan in Tracker")
objField.Orientation = xlPageField
objField.Caption = "Tactical Plan"
Set objField = objTable.PivotFields("2016 Invoice Quantity")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "#,##0"
Set objField = objTable.PivotFields("2016 VPM")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"
Set objField = objTable.PivotFields("2016 Revenue")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"
End Sub
Last edited: