Parsing pivot items in Excel Macro..

[arunsankars]

Hi,

In our project, we have a scenario as follows.

The output is a report with data fetched through a Pivot Table (Base data is in a cube). We enter the department numbers in the MS ACCESS Form. The data is stored in a table. This data is taken as lookup data to the report. In the report, We need select the Departments that are present in the lookup. I tried the following.

Dim First as integer

First = 0

Windows("Report.xls).Activate
Sheets("Lookup").select
Range("D1").select
ActiveCell.FormulaR1C1 = "=(R[1]C[-3])"

Do until ActiveCell.value = 0

Windows("Report.xls).Activate
Sheets("Lookup").select
Range("D1").select
DepCode = ActiveCell.value

Windows("Emp.xls").Activate
Sheets("Employee Data").Select

With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[DepartmentNumber].[DepartmentNumber]")
.CubeField.EnableMultiplePageItems = True
Dim pivotchoice
For Each pivotchoice in .PivotItems  -  Line to check for the pivot items
If pivotchoice = DepCode then
If First = 0 Then 
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[DepartmentNumber].[DepartmentNumber]")
.AddPageItem
"[DepartmentNumber].[DepartmentNumber].[Depno].&[pivotchoice]",True
First = 1
End with
else
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[DepartmentNumber].[DepartmentNumber]")
.AddPageItem
"[DepartmentNumber].[DepartmentNumber].[Depno].&[pivotchoice]"
End with
End if
End if
Next
End With

when i run through the macro, No values getting populated into pivotchoice. The pivot items are not parsed. I am not sure whats wrong with this code.

Please let me know how to fix this...

Thanks,
Arun