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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,035
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,035
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,577
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top