Set Pivot items on multiple (numeric value) cells

[remonvdd]

Hi,

I am struggling with a macro to set multiple pivot items based on multiple cells. I have tried different codes but they don't work as they should be.
In this case, I would like the macro to set the months based on cells "'Validatie'!AQ2" and "'Validatie'!AQ3". These cells contain numeric values (month numbers).

For example, the cells contain the values 10 (AQ2) and 9 (AQ3). I want the macro to only select the values 9 and 10 as Pivot item within the pivot field "Maand".

The code that I have (it outputs the value fron AQ2):

Sub filter_mom()


Dim item As String, it As PivotItem
item = Range("'Validatie'!AQ2, 'Validatie'!AQ3").Value


ActiveSheet.PivotTables("Draaitabel1").PivotFields("Maand"). _
        ClearAllFilters


For Each it In ActiveSheet.PivotTables("Draaitabel1").PivotFields("Maand").PivotItems
    If item = it.Name Then
        it.Visible = True
    Else
        it.Visible = False
    End If
Next it


End Sub

Thanks for your help!

 

[Domenic]

Try...

[color=darkblue]Sub[/color] filter_mom()

    [color=darkblue]Dim[/color] vPivotItemValue [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] oPivotItem [color=darkblue]As[/color] PivotItem
    [color=darkblue]Dim[/color] rItems [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rItem [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] bFound [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
    
    [color=darkblue]Set[/color] rItems = Range("'Validatie'!AQ2, 'Validatie'!AQ3")
    
    [color=darkblue]With[/color] ActiveSheet.PivotTables("Draaitabel1").PivotFields("Maand")
        .ClearAllFilters
        [color=darkblue]For[/color] [color=darkblue]Each[/color] oPivotItem [color=darkblue]In[/color] .PivotItems
            [color=darkblue]If[/color] IsNumeric(oPivotItem.Value) [color=darkblue]Then[/color]
                vPivotItemValue = [color=darkblue]CDbl[/color](oPivotItem.Value)
            [color=darkblue]Else[/color]
                vPivotItemValue = oPivotItem.Value
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]For[/color] [color=darkblue]Each[/color] rItem [color=darkblue]In[/color] rItems
                [color=darkblue]If[/color] rItem.Value = vPivotItemValue [color=darkblue]Then[/color]
                    bFound = [color=darkblue]True[/color]
                    [color=darkblue]Exit[/color] [color=darkblue]For[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]Next[/color] rItem
            [color=darkblue]If[/color] bFound [color=darkblue]Then[/color]
                bFound = [color=darkblue]False[/color]
            [color=darkblue]Else[/color]
                oPivotItem.Visible = [color=darkblue]False[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] oPivotItem
    [color=darkblue]End[/color] [color=darkblue]With[/color]
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[remonvdd]

Yes, this works perfectly! Thanks!!