Hello,

I am trying to automatically filter a specific Pivot Table Field by any items that contain "12 month" or "24 month". I have originally tried to record a macro that goes through and manually ticks them all (resulting in a very long array) but this broke if one of the items in my array wasn't present. I have then done some research and come across a code similar to below which i added a couple of extra details to in an attempt to get it to filter multiple times but this doesn't work.

Range("A100").Select
    ActiveCell.FormulaR1C1 = "12 month"
    Range("A101").Select
    ActiveCell.FormulaR1C1 = "24 month"
    Dim pt As PivotTable
Dim Field As PivotField
Dim NewCat As String

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set Field = pt.PivotFields("[Range].[Rateplan Name Desc].[Rateplan Name Desc]")
NewCat = "*" & ActiveSheet.Range("A100").Value & "*"
NewCat1 = "*" & ActiveSheet.Range("A101").Value & "*"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("[Range].[Rateplan Name Desc].[Rateplan Name Desc]")
    .ClearAllFilters
.PivotFilters.Add Type:=xlCaptionContains, Value1:=ActiveSheet.Range("A100").Value
.PivotFilters.Add Type:=xlCaptionContains, Value1:=ActiveSheet.Range("A101").Value
If i remove the last line, then it will successfully filter based on Cell A100, but in a weird way. It will only show results on the Pivot Table that contain "12 month" it wont ACTUALLY filter by this caption (the filter shows everything is still ticked). This becomes an issue if people may want to filter the results later on. We just need it to automatically filter it on the first run of the sub (of which this is one part of the code).

Please advise if I am going about this the completely wrong way. I have found similar threads before for multiple filters but these are for filtering multiple fields not the same field with multiple aspects. I am using Excel 2016.