Pivottable data column field

[Djblois]

When you add multiple items to a data field in pivot tables it gives you the option to add it to the column instead of the row. I would like to do this with code. This is the code I am using now and it doesn't work:

If PivotTableOptions.Column = True Then
If PivotTableOptions.Cases.Value = True Then
With pt.PivotFields("Cases")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If
If PivotTableOptions.Units.Value = True Then
With pt.PivotFields("Units")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If
If PivotTableOptions.Amount.Value = True Then
With pt.PivotFields("Amt ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If
If PivotTableOptions.Cost.Value = True Then
With pt.PivotFields("Total Cost ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If
If PivotTableOptions.Profit.Value = True Then
With pt.PivotFields("Profit ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If
If PivotTableOptions.Price.Value = True Then
With pt.PivotFields("Price ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "Comma"
End With
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If
End If

[matthewspatrick]

Try something like:



Dim DataFldCount As Long
If PivotTableOptions.Column = True Then
If PivotTableOptions.Cases.Value = True Then
With pt.PivotFields("Cases")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
DataFldCount = DataFldCount + 1
End If
If PivotTableOptions.Units.Value = True Then
With pt.PivotFields("Units")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
DataFldCount = DataFldCount + 1
End If
If PivotTableOptions.Amount.Value = True Then
With pt.PivotFields("Amt ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
DataFldCount = DataFldCount + 1
End If
If PivotTableOptions.Cost.Value = True Then
With pt.PivotFields("Total Cost ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
DataFldCount = DataFldCount + 1
End If
If PivotTableOptions.Profit.Value = True Then
With pt.PivotFields("Profit ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
DataFldCount = DataFldCount + 1
End If
If PivotTableOptions.Price.Value = True Then
With pt.PivotFields("Price ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "Comma"
End With
DataFldCount = DataFldCount + 1
End If
If DataFldCount > 0 Then
pt.DataPivotField.Orientation = xlColumnField
End If
End If