Pivot Table programming problem
 

Dim pvt as PivotTable
Dim pitm as PivotItem
Dim s as String
s = "abc"
Set pvt = ActiveSheet.PivotTables("PivotTable1")
For Each pitm In pvt.PageFields("Project").PivotItems
If lcase(pitm.Value) = lcase(s) Then
pvt.PageFields(1).CurrentPage = pitm.Value
Exit For
End If
Next

I usually try to use the value of the pivot item to actually set the
pagefield as I have had problems myself trying to set it to the string value
(eventhough it appears to be a match).


--
Regards,
Tom Ogilvy



"DoctorG" wrote:

I have set up a Pivot with a page field (ProjectCode) so as to total 5
different fields for a given Project. I then use a Module Level Public
Function to change this
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Project").CurrentPage =
<the function parameter and return the 5 values to the program that called
the Function.

If the parameter value exists in the Pivot field values my code works fine.
If it does not exist in the list though I get an error message at runtime. Is
there a way to get around this situation? How can I test if the parameter
exists in the list of the Pivot field values?

Pivot Table programming problem
 

Thanks a lot Tom... A precise and complete reply, as always.

"Tom Ogilvy" wrote:

Dim pvt as PivotTable
Dim pitm as PivotItem
Dim s as String
s = "abc"
Set pvt = ActiveSheet.PivotTables("PivotTable1")
For Each pitm In pvt.PageFields("Project").PivotItems
If lcase(pitm.Value) = lcase(s) Then
pvt.PageFields(1).CurrentPage = pitm.Value
Exit For
End If
Next

I usually try to use the value of the pivot item to actually set the
pagefield as I have had problems myself trying to set it to the string value
(eventhough it appears to be a match).


--
Regards,
Tom Ogilvy



"DoctorG" wrote:

I have set up a Pivot with a page field (ProjectCode) so as to total 5
different fields for a given Project. I then use a Module Level Public
Function to change this
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Project").CurrentPage =
<the function parameter and return the 5 values to the program that called
the Function.

If the parameter value exists in the Pivot field values my code works fine.
If it does not exist in the list though I get an error message at runtime. Is
there a way to get around this situation? How can I test if the parameter
exists in the list of the Pivot field values?