1
I have two dynamic tables and I want to always select the same item in them, which in this case are names. Then I have a rule that takes in another sheet the names and on the side the number of pending, if the person has more than 20 pending it should be marked automatically in the two dynamic tables, but only it. And no one else, but I can’t make him go around doing it one by one. Sometimes it goes, sometimes it doesn’t.
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
VBA accuses some error message? What exactly goes wrong?
– dHEKU
for example, the Myarray(i) = "Pedro" there he should go in the two dynamics and select only the peter, and in the next iteration, the other employee who has more than twenty pending, but he does not mark only one person at a time, he ends up marking others. I wanted a code that unchecks everything, and marks only the index at a time.
– alexandre