VBA to apply a filter to a pivot table

[Moosles]

I'm looking to apply a filter to a PivotTable using VBA but I'm stuck on what I hope is a simple error on my part. The source of my pivot data covers ~10000 rows of data, and the column that I'm applying the filter to is called "Mapping Code". I am looking to filter this part of the pivot to only contain data that has "PTC" in the mapping code, but this series of letters can appear anywhere in the Mapping Code. I cannot change the source data in any way as this is in constant use so I need to apply the filter to the PivotTable.

The code that I'm getting an error at occurs here:

With ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Mapping Codes")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Mapping Codes").CurrentPage = "(All)"
    
    With ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Mapping Codes")
        .PivotItems("*" & "PTC" & "*").Visible = True

    End With

The error occurs at the

.PivotItems("*" & "PTC" & "*").Visible = True

point in the code.

To be clear, I am looking to apply a filter that contains the letters PTC at any point in the mapping code. When I tried recording the macro to apply the filter it explicitly spelled out the roughly 700 Mapping Codes that do not contain PTC, which is hugely inefficient and not practical (as the Mapping Codes will not be consistent). How should I go about fixing this?

 

[RoryA]

You can't use that kind of filtering in a page/filter field. You'd need to use it in a row or column field. Otherwise, you will need to create a list of valid entries.

 

[Moosles]

Thanks Rory - I tried applying this to a Row Field, and it's still not working. The fields I have are Client Mapping Codes (which I want to remain unfiltered); Mapping Codes (that I want to filter to just show rows with "PTC" in them); and the Sum field:

    With ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Client Mapping Codes")
        .Orientation = xlRowField
        .Position = 1
    End With
        
    With ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Mapping Codes")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ActiveSheet.PivotTables("PivotTablePTC").AddDataField ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Total Cost (" & BiddingCurrency & ")"), "Sum of Total Cost", xlSum
        With ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Sum of Total Cost")
            .NumberFormat = "#,##0.00"
        End With
              
    With ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Mapping Codes")
        .PivotItems("*" & "PTC" & "*").Visible = True

    End With

Again it is breaking in the same place, what am I doing wrong?

 

[RoryA]

That syntax won't work. You need to add a filter like this:

ActiveSheet.PivotTables("PivotTablePTC").PivotFields("Mapping Codes").PivotFilters.Add2 Type:=xlCaptionContains, Value1:="PTC"

 

[Moosles]

Exactly what I was looking for, thanks!