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

kmach60

New Member
Joined
Jan 11, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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 :)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

kmach60

New Member
Joined
Jan 11, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Sorry, I made a typo in my InStr function, changes to pf but still doesnt work :(

VBA Code:
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,524
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...

VBA Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,207
Messages
5,640,848
Members
417,173
Latest member
Tuanphun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top