VBA Pivot Table- Datafields Horizontal to Vertical

[IvyZhou]

I've been following a book on how to create a pivot table using VBA. The code in the book works fine and it's doing what it's supposed to be doing. But i don't understand how to change how the Datafields are displayed. It's listing the datafields horizontally one on top of the other in One column. I want to know how to get the datafields to appear in separate columns (in the datafield area)



Sub CreatePivot2()
' Create pivot table at active cell
' Assumes that the source table is in sheet called Source
ActiveSheet.PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Source'!R1C1:R145C7", _
TableName:="Sales&Trans2"
ActiveSheet.PivotTables("Sales&Trans2").AddFields _
RowFields:=Array("Store City","Store Type"), _
ColumnFields:="Period", _
PageFields:="Year"
With ActiveSheet.PivotTables("Sales&Trans2")
With.PivotFields("Transactions")
.Orientation = xlDataField
.Function= xlSum
.Position =1
EndWith
With.PivotFields("Sales")
.Orientation = xlDataField
.Function= xlSum
.Position =2
EndWith
EndWith
End Sub

[Paul_Hossler]

Add this towards the end




.PivotFields("Data").Orientation = xlColumnField

[IvyZhou]

Hi Paul, Thank you very very much!! Now it works well~~