Error handling pivot field VBA

[muhleebbin]

Hi!

I have the following code however on occasion I do run into an error when the pivot field (cat) that the code is looking for does not exist. Can someone help with the error handling code that would eliminate this?

Thank you in advance!

    Dim pt As PivotTable
    Dim field As PivotField
    Dim res As PivotField
    Dim cat As String

    Application.ScreenUpdating = False
    
    Set pt = Worksheets("MIR Pivot").PivotTables("PivotTable1")
    Set field = pt.PivotFields("PropertyName")
    Set res = pt.PivotFields("Resident")
    cat = Worksheets("MIR Pivot").Range("G2").Value

    With pt
        field.ClearAllFilters
        field.CurrentPage = cat
        pt.RefreshTable
    End With

 

[Domenic]

It looks fine, so I've re-written it so that the error handling is done slightly differently. Maybe it might provide us with some clue as to what is happening. So try this instead...

    With field
        .ClearAllFilters
        
        On Error Resume Next
        Set PvtItm = .PivotItems(cat)
        On Error GoTo 0
        
        If Not PvtItm Is Nothing Then
            .CurrentPage = cat
        Else
            .CurrentPage = "(blank)"
        End If

    End With

Does it work as expected? Or does it give you some error? If so which one and on which line?

 

[muhleebbin]

It highlights .CurrentPage = cat after this

 

[Domenic]

Okay, so when that happens, move your cursor over the variable cat. What does it display? Does the value that is displayed appear in the drop-down for your page field?

Actually, hold on a second, you're probably getting that error because With field is not referencing a page field. Can you please confirm whether in fact it is a page field?

 

[muhleebbin]

Hovering over cat it does say cat = "Vancouver" which is not a drop down option at that time....the more recent data does have Vancouver data

The With field currently references to the PivotField "PropertyName" (field = "PropertyName" when hovered over the with statement)

 

[Domenic]

Then simply refresh your pivot table before setting the current page for your page field. So maybe like this...

    pt.RefreshTable
    
    With field
    
        .ClearAllFilters
    
        On Error Resume Next
        .CurrentPage = cat
        If Err.Number <> 0 Then
            .CurrentPage = "(blank)"
        End If
        On Error GoTo 0
  
    End With

Does this help

 

[muhleebbin]

This worked!!!

Thank you so very much for your help Domenic!

 

[Domenic]

You're very welcome, cheers!