RTE 1004 when filtering pagefield via macro

[Henning]

Hello,
I am currently trying to make a simple button that filters a PageField in a Pivot table.

Worksheets("SN VIEW (2)").PivotTables("SN").PivotFields("DELIVERY SEQUENCE").PivotFilters.Add Type:=xlCaptionContains, Value1:="*ONSITE*"

This will give me a
Run-time error '1004':
Application-defined or object-defined error

Ive also tried this:

Dim PvtTbl As PivotTable
Set PvtTbl = Sheets("SN VIEW (2)").PivotTables("SN")
PvtTbl.PageFields("DELIVERY SEQUENCE").ClearAllFilters
PvtTbl.PageFields("DELIVERY SEQUENCE").EnableMultiplePageItems = True
PvtTbl.PageFields("DELIVERY SEQUENCE").PivotFilters.Add Type:=xlCaptionContains, Value1:="*ONSITE*"

This will run once, remove all the filters and then ignore the last row of code.
If you press it again, it will also give you the same run-time error.

The PageField "DELIVERY SEQUENCE" contains a load of strings, like so:
01. Remote With Parts, 02. Subcontractor Onsite
01. Subcontractor Onsite
01. Remote With Parts, 02. Subcontractor Onsite
01. Subcontractor Onsite
01. Subcontractor Onsite, 02. Subcontractor Onsite
01. Subcontractor Onsite, 02. Subcontractor Onsite
01. Subcontractor Onsite
01. Subcontractor Onsite, 02. Subcontractor Onsite, 03. Subcontractor Onsite
01. Depot(Repair and Return), 02. Subcontractor Onsite


I now want to get it to ON_Click "select multiple selections that all contain "Onsite"".

When I try to record that with the macro recorder, it simply ignores my selection of "Select multiple items" and then subsequent actual selection of multiple items.

Does anyone know what is missing, so everything is defined?

thanks in advance,
Henning

 

[Andrew Poulsom]

You can't use PivotFilters with a PageField, manually or in VBA.

 

[Henning]

You can't use PivotFilters with a PageField, manually or in VBA.

Hello Andrew, thank you for your quick response. Do you maybe know of a workaround? Or is there simply no way?

Because I know you can set pagefields to single values directly:

Sheets("SN VIEW (2)").PivotTables("SN").PivotFields("DI ONSITE (>2) ALL").CurrentPage = "1"

This works, but only for one value.

 

[Andrew Poulsom]

You could move the PageField to the Row area, apply the filter and move it back to the Page area.

 

[Henning]

I got it. So amazing how much you can code without knowing how to, just buy knowing what to google.
Thanks Andrew.

Below the code to set pagefields to multiple selections that contain a certain string:

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = Sheets("SN VIEW (2)").PivotTables("SN")
Set pf = pt.PivotFields("DELIVERY SEQUENCE")
    pt.ManualUpdate = True
    Application.ScreenUpdating = False
    With pf
        On Error Resume Next
       
    
        For Each pi In pf.PivotItems
             pi.Visible = False
    Next pi
        
        For Each pi In pf.PivotItems
            If pi.Value Like "*Onsite*" Then
                pi.Visible = True
            Else: pi.Value = False
            End If
        Next pi
       
    End With
  pt.ManualUpdate = False
Application.ScreenUpdating = True