Finding CurrentPage in PivotTable
 

Hi there,

Is there a way to test to see if an Item is in the list of PivotField,
before setting the CurrentPage to that item? The reason I ask is if you don't
check first to make sure the item exists, then set the CurrentPage to the
item you want, it creates that item for you, which is not what I want it to
do.

Cheers,
Grant.

ActiveWorkbook.Worksheets(wkSheet.Name).PivotTable s(pvtTable.Name).PivotFields("Line").CurrentPage = sStr

Finding CurrentPage in PivotTable
 

You can check if the item exists:

'========================
Sub ChangePivotPage()
On Error Resume Next
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim str As String
Set ws = ActiveSheet
Set pt = ws.PivotTables(1)
str = ws.Range("E2").Value

With pt.PageFields("Rep")
'Test if Item exists
Set pi = .PivotItems(str)
On Error GoTo 0
If pi Is Nothing Then
MsgBox str & " is not an item in the Rep field"
Else
.CurrentPage = str
End If
End With

End Sub
'============================

Grant wrote:
Hi there,

Is there a way to test to see if an Item is in the list of PivotField,
before setting the CurrentPage to that item? The reason I ask is if you don't
check first to make sure the item exists, then set the CurrentPage to the
item you want, it creates that item for you, which is not what I want it to
do.

Cheers,
Grant.

ActiveWorkbook.Worksheets(wkSheet.Name).PivotTable s(pvtTable.Name).PivotFields("Line").CurrentPage = sStr


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html