G
Geoff
I cannot get the syntax right when trying to add PivotItems to a PivotField.
Given there are 4 possible items in a field ie L E S and (blank) I want to
ensure there are always 3 PivotItems in the resulting PTable whether there
are any values or not (I will add zeros where necessary).
1. The code below fails at the Cubefields statement with error 1004
2. Because of the fail I cannot determine whether the construct code should
be inside the With .PivotFields("L E S") procedure or outside.
Can anyone help please?
Using XL2003 and given:
ActiveWorkbook.Sheets(5).PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:=Sheets(5).Range("CM22"), _
TableName:="PivotTable6"
With Sheets(5).PivotTables("PivotTable6")
With .PivotFields("T")
.Caption = "Day"
''''etc etc
End With
With .PivotFields("L E S")
.Orientation = xlDataField
.Position = 1
End With
The construct then is :
If Not .PivotFields("L E S").PivotItems.Count > 1 Then
'''''''''''''''''''''''''''''' goes wrong here
With ActiveSheet.PivotTables("PivotTable6").PivotFields("[L E S]")
.CubeFields.EnableMultiplePageItems = True
End With
'''''''''''''''''''''''''''''' then something like
Do
.AddPageItem "[L E S].[xxxxx]"
Until .PivotFields("L E S").PivotItems.Count = 3
''''''''''''''''''''''''''''' then give titles to the created PivotItems
End if
End With
T.I.A.
Geoff
Given there are 4 possible items in a field ie L E S and (blank) I want to
ensure there are always 3 PivotItems in the resulting PTable whether there
are any values or not (I will add zeros where necessary).
1. The code below fails at the Cubefields statement with error 1004
2. Because of the fail I cannot determine whether the construct code should
be inside the With .PivotFields("L E S") procedure or outside.
Can anyone help please?
Using XL2003 and given:
ActiveWorkbook.Sheets(5).PivotTables("PivotTable1").PivotCache. _
CreatePivotTable TableDestination:=Sheets(5).Range("CM22"), _
TableName:="PivotTable6"
With Sheets(5).PivotTables("PivotTable6")
With .PivotFields("T")
.Caption = "Day"
''''etc etc
End With
With .PivotFields("L E S")
.Orientation = xlDataField
.Position = 1
End With
The construct then is :
If Not .PivotFields("L E S").PivotItems.Count > 1 Then
'''''''''''''''''''''''''''''' goes wrong here
With ActiveSheet.PivotTables("PivotTable6").PivotFields("[L E S]")
.CubeFields.EnableMultiplePageItems = True
End With
'''''''''''''''''''''''''''''' then something like
Do
.AddPageItem "[L E S].[xxxxx]"
Until .PivotFields("L E S").PivotItems.Count = 3
''''''''''''''''''''''''''''' then give titles to the created PivotItems
End if
End With
T.I.A.
Geoff