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