Prevent Setting .CurrentPage to value that does not exist in PivotField.


I have the following VBA to set the .CurrentPage (of the PivotTable Filter "Item Code") to a variable.

Sheets("Purchase Detail").PivotTables("PurchasePT").PivotFields("Item Code").CurrentPage = ItemCodeVariable

Open in new window

If ItemCodeVariable does not equal something that already exists in the the field "Item Code", I want it to fail and return a message. Currently, it will just set the page equal to the variable (effectively doing nothing except being confusing).

For example, "Item Code" would only be loaded with "100", "200","300","400". If I set ItemCodeVariable = 500, it should not work. (In reality, I have thousands of Item Codes).

Any help would be greatly appreciated.
Who is Participating?

pls try

Sub Macro()
bFound = False
Set pvtItems = Sheets("Purchase Detail").PivotTables("PurchasePT").PivotFields("Item Code").PivotItems
For Each pvtItem In pvtItems
    If pvtItem.Value = ItemCodeVariable Then
        bFound = True
        Exit For
    End If
If bFound Then
    Sheets("Purchase Detail").PivotTables("PurchasePT").PivotFields("Item Code").CurrentPage = ItemCodeVariable
    MsgBox "Error: Not Found"
End If
End Sub

Open in new window

Rory ArchibaldCommented:
Branching out from Excelforum? ;)

You could also use a simple error handler:

Dim pi as pivotitem
with Sheets("Purchase Detail").PivotTables("PurchasePT").PivotFields("Item Code")
on error resume next
Set pi = .Pivotitems(ItemCodeVariable)
on error goto 0
if not pi is nothing then .CurrentPage = ItemCodeVariable
end with

Open in new window

EnigmaMatterAuthor Commented:

ExcelForum is always the forum I recommend to others for Excel; I have never had a resource help me more in my entire college experience and career. But, I pay $25/Month for Experts Exchange, so I figured I ought to use it.

The one thing that Experts Exchange has is private questions -- makes me feel more comfortable talking because of the very proprietary nature of what I do.

I will try out these solutions!
EnigmaMatterAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.