Trying to set a pivot field to a subset of values listed in range of cells
I am trying to create a macro that when run will deselect non matching
values in a pivot field. I want the macro to look at a range I have in another sheet and deselect the values that do not match in the pivot field and cell range. The code is below. I know the error lies in my "not equal" comparison of the pivotfield to my cell range, but I do not know how to fix it. I also have the cell range "L2:L63" named "MDS_MIX", but also could not get that reference to work. Thanks for the help. Public Sub 24_MIX_Click() On Error GoTo X Application.ScreenUpdating = False Dim MyMDS As Variant Dim MyValue As Variant Dim MyArray() As Variant Dim MyList() As Variant MyList = Sheets("Converters").Range("L2:L63") MyValue = Sheets("Converters").Range("O1") For i = 1 To MyValue If ActiveSheet.PivotTables("PivotTable1").PivotFields ("MDS").PivotItems(i).Value < MyList Then ActiveSheet.PivotTables("PivotTable1").PivotFields ("MDS").PivotItems(i).Visible = False End If Next i Application.ScreenUpdating = True X: AddAll_MDS_Click Application.ScreenUpdating = True End Sub |
All times are GMT +1. The time now is 08:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com