Show only matches between a named cell range and the values in a page field - vba
I'm trying to create a macro in vba that will compare a list of items
in a named cell range to the values in a page field of a pivot table.
The named cell range is a subset of the complete list of values in the
page field of the pivot table. I want to hit a button and the pivot
table will automatically select only the subset of values. Any ideas?
I tried this, but the comparison between the pivot item value and the
array is obviuously not working.
Public Sub A4_24_MIX_Click()
Dim MyValue As Variant
Dim MyArray() As Variant
MyValue = Sheets("Converters").Range("O1")
'i'm not even sure how to reference a named cell range - the cells
named range is "A4_MIX" and are cells L2:L63
MyArray = ActiveWorkbook.Range(A4_MIX)
For i = 1 To MyValue
If
ActiveSheet.PivotTables("PivotTable1").PivotFields ("MDS").PivotItems(i).Value
< MyArray Then
ActiveSheet.PivotTables("PivotTable1").PivotFields ("MDS").PivotItems(i).Visible
= False
End If
Next i
End Sub
|