Multiple Items selection in a Pivot VBA Code

James Burger

New Member
Joined
Jun 10, 2014
Messages
31
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
See if this does what you want

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,935
Members
409,847
Latest member
Foster034
Top