Multiple Items selection in a Pivot VBA Code

[James Burger]

Hi all,

Just like to enquire if this is possible please.

I would like a seperate box say from A1:A4 I can input my values like:

USER INPUT Value:[A1]

London [A2]

New York [A3]

Paris [A4]

<TBODY>
</TBODY>

Then whatever values I have put under A2:A4 I would like to use this as a multiple criteria for a Report Filer I have in the pivot called "Location" - so the pivot for the "Location" filter will only select values for London, New York and Paris.

I can do this for 1 values by using something like this:

Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Location").CurrentPage = Sheets("Sheet1").Range("A2").Text

But I don't know how to do it for multiple as above...

Your help will be much appreciative.

JB

 

[Marcelo Branco]

See if this does what you want

Sub aTest()
    Dim PTField As PivotField, pvi As PivotItem, rngInput As Range, rngFound As Range
    
    With Sheets("Sheet1")
        Set rngInput = .Range("A2:A4")
        Set PTField = .PivotTables("PivotTable1").PivotFields("Location")
    End With
    
    With PTField
        If Application.CountA(rngInput) Then
            .EnableMultiplePageItems = True
            For Each pvi In .PivotItems
                Set rngFound = rngInput.Find(pvi, LookAt:=xlWhole, LookIn:=xlValues)
                On Error GoTo NoneFound
                pvi.Visible = Not (rngFound Is Nothing)
            Next pvi
        Else
            .ClearAllFilters
            .EnableMultiplePageItems = False
        End If
    End With
    
    Exit Sub
NoneFound:
    MsgBox "No location was found" & vbNewLine _
            & "Please, enter valid locations in A2:A4"
    PTField.ClearAllFilters
    PTField.EnableMultiplePageItems = False
End Sub

Hope this helps

M.

 

[James Burger]

This is blowing my mind - thanks so much Marcelo!!!

 

[Marcelo Branco]

This is blowing my mind - thanks so much Marcelo!!!

You are welcome and thanks for the feedback.
M.
ps: forgot to say: if the user let rngInput (A2:A4) empty, or put only non-existent locations, then all items will be selected