Making multiple pivot items visible depending case

Question

what I want to do is that based in a cell the pivot items change

For example if the selected cell has February as value I want that every pivot table in the field month filter by Jan and Feb or

if the selected cell has May as value I want that every pivot table in the field month filter by Jan, Feb, Mar, April and May

This is the code that I have but the problem is that shows problems with the property visible of pivotitem

Then I assign the different cases in the code

Sub skicer()

Dim PT As PivotTable
Dim ws As Worksheet
Dim Field As PivotField

Set ws = Worksheets("PyG Tecnico")

        For Each PT In ws.PivotTables
        'MsgBox PT.Name
        Set Field = PT.PivotFields("Month")
        Field.ClearAllFilters
        With Field
            .PivotItems("Jan").visible = True
            .PivotItems("Feb").visible = False
            .PivotItems("Mar").visible = False
            .PivotItems("April").visible = False
            .PivotItems("May").visible = False
            .PivotItems("Jun").visible = False
            .PivotItems("Jul").visible = False
            .PivotItems("August").visible = False
            .PivotItems("Sept").visible = False
            .PivotItems("Oct").visible = False
            .PivotItems("Nov").visible = False
            .PivotItems("Dic").visible = False
        End With
    Next PT

End Sub

Thanks for your help

If it's based on the same data cache you may be able to just use a timeline slicer, no? — Mark S.
@MarkS. They are different datasets and these can't join. Thx — Cesar_Torres

Tim Williams Tim Williams · Accepted Answer · 2020-01-30T17:17:22

One approach:

Sub skicer()

    Dim PT As PivotTable
    Dim Field As PivotField, mSel, bShow As Boolean, m

    mSel = Selection.Cells(1).Value 'selected month

    For Each PT In Worksheets("PyG Tecnico").PivotTables

        Set Field = PT.PivotFields("Month")
        Field.ClearAllFilters
        bShow = True

        For Each m In Array("Jan", "Feb", "Mar", "April", "May", "Jun", "Jul", _
                            "August", "Sept", "Oct", "Nov", "Dic")

            Field.PivotItems(m).Visible = bShow
            If m = mSel Then bShow = False 'later months do not show...

        Next m

    Next PT

End Sub

Making multiple pivot items visible depending case

1 Answers