Djblois
09-09-2006, 09:20 AM
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
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