Checking all Pivot Fields in Pivot Table for string match to use as filter for pivot table

[kmach60]

Hi everyone!

New to the forum, was hoping I good get some help with something. Thanks in advance! I tried to google this but couldn't find the answer I needed.

I have a pivottable "PivotTable1" that gets automatically created from a set of data that changes, the issue here is that I want to target a specific Pivot Field every time, but the name of that field changes slightly.

The Pivot Field I'm trying to target is either going to be "HOL DSP DAY" or "HOL DSP", so I'm trying to write a macro that searches through all the Pivot Fields in the Pivot Table and checks if the name contains "HOL DSP" and then set that Pivot Field as the filter for the Pivot Table.

Sub PivotFilter()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim dspField As PivotField
    
    Set pt = ActiveSheet.PivotTables("PivotTable1")

    For Each pf In pt.PivotFields
        If InStr(pt.Name, "HOL DSP") > 0 Then
            Set dspField = pf
        End If
    Next pf

    With pt.dspField
        .Orientation = xlPageField
        .Position = 1
    End With
    
End Sub

When I run this code I get Run-Time error '438': Object doesn't support this property or method

This is what I've come with so far, sorry I'm a little new to VBA, still trying to figure things out. I'm not even sure if this is the right approach for what I'm trying to accomplish.

Any help would be appreciated!

Thank you

 

[kmach60]

Sorry, I made a typo in my InStr function, changes to pf but still doesnt work

Sub PivotFilter()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim dspField As PivotField
    
    Set pt = ActiveSheet.PivotTables("PivotTable1")

    For Each pf In pt.PivotFields
        If InStr([COLOR=rgb(247, 218, 100)]pf[/COLOR].Name, "HOL DSP") > 0 Then
            Set dspField = pf
        End If
    Next pf

    With pt.dspField
        .Orientation = xlPageField
        .Position = 1
    End With
    
End Sub

 

[Domenic]

Try replacing With pt.dspField with With dspField. By the way, with regards to your For Each/Next loop, once you've found your target pivot field, you can exit the loop. Try...

Sub PivotFilter()

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim dspField As PivotField
    
    Set pt = ActiveSheet.PivotTables("PivotTable1")

    For Each pf In pt.PivotFields
        If InStr(pf.Name, "HOL DSP") > 0 Then
            Set dspField = pf
            Exit For
        End If
    Next pf

    With dspField
        .Orientation = xlPageField
        .Position = 1
    End With
    
End Sub

Hope this helps!