VBA ... On Error Resume Next ... Pivot Table

[Peter]

Hi,

I use pivots extensively in certain automated reports, and need to select
and deselect certain items at different times to get the correct figures.

I usually set the .visible property of each pivotitem to whatever I need
it to be, but if you try and set a .visible to True when it is already
True, an error is generated. In order to bypass this, I usually set "On
Error Resume Next" during the process, and then once the .visible property
is correctly set, I run "On Error Goto ErrorMsg" (ErrorMsg being my error
trapping routine). All works perfectly

When I try the same thing with .CurrentPage, it doesn't work well at all,
and this is where my problem comes in. My code snippet is as follows:

*********************************************************************************
Do While ActiveCell.Value <> ""
On Error Resume Next
Select Case ActiveCell.Value
Case Is = "Urgent"
Worksheets("Pivot").PivotTables("Pivot_Calls").PivotFields("Root_Priority").CurrentPage
= 3
Case Is = "High"
Worksheets("Pivot").PivotTables("Pivot_Calls").PivotFields("Root_Priority").CurrentPage
= 2
Case Is = "Medium"
Worksheets("Pivot").PivotTables("Pivot_Calls").PivotFields("Root_Priority").CurrentPage
= 1
Case Is = "Low"
Worksheets("Pivot").PivotTables("Pivot_Calls").PivotFields("Root_Priority").CurrentPage
= 0
End Select
On Error GoTo ErrorMsg
<...Other Code Goes Here ...>
Loop
***********************************************************************************

If there is no value "3" in the PivotCache for Root_Priority, then the
code should just Resume Next, but it doesn't ... it also doesn't go to
ErrorMsg, instead it comes up with a system error: "Unable to set the
_Default property of the PivotItem class"

Could the resident gurus please help me with:
1.) Why isn't resume next firing? (& what to do about it!)
2.) Is there a better way to do the above?
3.) Are there any online resources dealing with coding/handling Pivot
Tables.

Just to round out the picture:
Windows 2000 Server, SR4
MS Office XP (2002)

Thanks,
Peter Bellis
Sunny South Africa

 

[Tom Ogilvy]

If you set the pagefield value and it isn't a valid pivot item value, then
in my experience you screw up your pivot table cache. I would loop though
the pivot item collection and test for the pivot item I want to set.

 

[Peter]

Thanks Tom.

Regards,
Peter Bellis