Adding PivotItems

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top