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.
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
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