Linking cells to multiple criteria Pivot table filtre

[ExcelBusCtrl]

Hi,

I have a pivot table that is based on a data set which is updated every month. The pivot table has a multiple criteria filtre in order to select the month ie 1/1/2017,2/1/2017,etc.

I want to select the months from an external cell, meaning that if I type in 5 that cell the pivot table filtre should include all the months from January to Mai.

I manage to do that in the enclosed code, but is there a more rational way to write this code than with 12 If.. Then statements? Or is there another way of solving this problem?

Thanks for your help

Sub test_pivot()

    ActiveSheet.PivotTables("Pivottable1").PivotFields("Month").CurrentPage = _
        "(All)"
     
   If Range("periode") = 1 Then
   ActiveSheet.PivotTables("Pivottable1").PivotFields("Month").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("Pivottable1").PivotFields("Month")
        .PivotItems("1/1/2017").Visible = True
        .PivotItems("2/1/2017").Visible = False
        .PivotItems("3/1/2017").Visible = False
        .PivotItems("4/1/2017").Visible = False
        .PivotItems("5/1/2017").Visible = False
        .PivotItems("6/1/2017").Visible = False
        .PivotItems("7/1/2017").Visible = False
        .PivotItems("8/1/2017").Visible = False
        .PivotItems("9/1/2017").Visible = False
        .PivotItems("10/1/2017").Visible = False
        .PivotItems("11/1/2017").Visible = False
        .PivotItems("12/1/2017").Visible = False
    Range("H13").Select
    End With
   End If
    
   If Range("periode") = 2 Then
   ActiveSheet.PivotTables("Pivottable1").PivotFields("Month").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("Pivottable1").PivotFields("Month")
        .PivotItems("1/1/2017").Visible = True
        .PivotItems("2/1/2017").Visible = True
        .PivotItems("3/1/2017").Visible = False
        .PivotItems("4/1/2017").Visible = False
        .PivotItems("5/1/2017").Visible = False
        .PivotItems("6/1/2017").Visible = False
        .PivotItems("7/1/2017").Visible = False
        .PivotItems("8/1/2017").Visible = False
        .PivotItems("9/1/2017").Visible = False
        .PivotItems("10/1/2017").Visible = False
        .PivotItems("11/1/2017").Visible = False
        .PivotItems("12/1/2017").Visible = False
    Range("H13").Select
    End With
 End If
    If Range("periode") = 3 Then
   ActiveSheet.PivotTables("Pivottable1").PivotFields("Month").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("Pivottable1").PivotFields("Month")
        .PivotItems("1/1/2017").Visible = True
        .PivotItems("2/1/2017").Visible = True
        .PivotItems("3/1/2017").Visible = True
        .PivotItems("4/1/2017").Visible = False
        .PivotItems("5/1/2017").Visible = False
        .PivotItems("6/1/2017").Visible = False
        .PivotItems("7/1/2017").Visible = False
        .PivotItems("8/1/2017").Visible = False
        .PivotItems("9/1/2017").Visible = False
        .PivotItems("10/1/2017").Visible = False
        .PivotItems("11/1/2017").Visible = False
        .PivotItems("12/1/2017").Visible = False
    Range("H13").Select
    End With
    End If

 

[AFPathfinder]

Something like this?

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Months").ClearAllFilters

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").PivotFilters.Add2 _
        Type:=xlCaptionIsLessThanOrEqualTo, Value1:=Sheets("Sheet1").Range("A1").Value

 

[ExcelBusCtrl]

Hi AFPathfinder,
I dont manage t get that code to work. At the second line I get "Run time error 1004."

Thanks for your contribution anyway!

 

[AFPathfinder]

Did you modify the Value1 bit to match the sheet and cell you are putting the month value? I'm thinking the error might be caused by the sheet reference if Sheet1 doesn't exist.

 

[ExcelBusCtrl]

Hi - yes I did, but still no luck. Anyways, it works with the initial code I posted, so I'll just stick with that. Thanks for your efforts!

 

[Marcelo Branco]

Maybe something like this

Sub test_pivot2()
    Dim pi As PivotItem
    
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Month").ClearAllFilters
    If Range("periode") > 0 Then
        For Each pi In ActiveSheet.PivotTables("Pivottable1").PivotFields("Month").PivotItems
            If Month(DateValue(Format(pi, "mm/dd/yyyy"))) > Range("periode") Then pi.Visible = False
        Next pi
    End If
End Sub

M.

 

[ExcelBusCtrl]

Hi Marcelo,

Your code works perfectly! Thank you so much for helping me with this.

Have a nice day /evening!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.