Filtering PivotTables in VBA

1

I have two PivotTables and I want to always select the same item in them, which in this case are names. So I have a rule that picks up the names in another worksheet and the number of pending issues, if the person has more than 20 issues, it should be marked automatically in the two pivot tables, but only the pivot table. And no other person, but I can not make him go doing it one by one. Sometimes it goes, sometimes it does not.

Sub dinamycs()
    Dim i, j As Integer
    Dim MyArray(1 To 152) As String
    i = 0
    j = 3
    Sheets("Pendência_Agenda").Activate
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    For Each MyCell In Selection
    j = j + 1
    If Cells(j, 2).Value > 20 And Not MyCell.Value = "" And Not MyCell.Value = "################" Then
        i = i + 1
        MyArray(i) = MyCell.Value
        Sheets("Dinâmicas").Activate
        ActiveSheet.PivotTables("Tabela dinâmica5").PivotFields("Escritório").CurrentPage = "(All)"
        With ActiveSheet.PivotTables("Tabela dinâmica5").PivotFields("Escritório")
            If i <= 1 Then
            .PivotItems(MyArray(i)).Visible = True
            Else
            .PivotItems(MyArray(i)).Visible = True
            .PivotItems(MyArray(i - 1)).Visible = False
            End If
        End With
        ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields("Externo").CurrentPage = "(All)"
        With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields("Externo")
            If i <= 1 Then
            .PivotItems(MyArray(i)).Visible = True
            Else
            .PivotItems(MyArray(i)).Visible = True

            .PivotItems(MyArray(i - 1)).Visible = False
            End If
        End With
    End If


    Next MyCell

End Sub
    
asked by anonymous 30.06.2015 / 13:28

0 answers