![]() |
Adding PivotItems
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 |
All times are GMT +1. The time now is 03:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com