Hi,
I have a document with a pivot table in my worksheet. The aim of my VBA code is to filter the pivot table based on an array of customers. Once I run the code its not filtering the table.
I can select one customer and run the code again and it selects all. I have attached the code and images to show the 1st column is the array of customers and to the right is the pivot table.
thanks,
Code
Sub Update_Click()
FilterArray = Application.Transpose(ActiveWorkbook.Sheets("Elite Act").Range("A1:A31").Value)
Dim myPivotField As PivotField
Set myPivotField = ActiveSheet.PivotTables("Elite").PivotFields("[Customers].[Customer].[Customer]")
myPivotField.ClearAllFilters
numberOfElements = UBound(FilterArray) - LBound(FilterArray) + 1
If numberOfElements > 0 Then
With myPivotField
For i = 1 To myPivotField.PivotItems.Count
j = 0
Do While j < numberOfElements
If myPivotField.PivotItems(i).Name = FilterArray(j) Then
myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = True
Exit Do
Else
myPivotField.PivotItems(myPivotField.PivotItems(i).Name).Visible = False
End If
j = j + 1
Loop
Next i
End With
End If
End Sub
