filter a pivot table with values in an array of cells?

[mrdobbs]

Does anyone know a good way (in Excel 2010) to filter a pivot table to show only certain rows based on the values in an array of cells on another worksheet? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Say my pivot table has row labels of "SalesRep" and values of "count of Orders", and I want to only see the results for specific (and often arbitrary) SalesRep(s). Is there a way to have Excel filter the pivot table in a way other than unselecting all the values and then hand checking the box next to the SalesRep we want to report on?<o></o>
<o></o>
Thanks (long time reader, first time poster),<o></o>
<o></o>
Matt<o></o>

 

[Jerry Sullivan]

Hi Matt,

I've seen a few people post variations of this question and thought it might be useful to make a function that could be called using the parameters:

pvtField: The PivotField to be filtered
varItemList: A Variant Array of the items to be Visible
​

Private Function Filter_PivotField(pvtField As PivotField, _
        varItemList As Variant)
    Dim strItem1 As String
    Dim i As Long
    On Error GoTo ErrorHandler:
    Application.ScreenUpdating = False
 
    strItem1 = varItemList(LBound(varItemList))
    With pvtField
        .PivotItems(strItem1).Visible = True
        For i = 1 To .PivotItems.Count
            If .PivotItems(i) <> strItem1 And _
                  .PivotItems(i).Visible = True Then
                .PivotItems(i).Visible = False
            End If
        Next i
        For i = LBound(varItemList) + 1 To UBound(varItemList)
            .PivotItems(varItemList(i)).Visible = True
        Next i
    End With
    Exit Function
ErrorHandler:
    MsgBox "Error while trying to process item: " & varItemList(i)
End Function

Below are two examples of how to call the function.

The first uses a hard-coded array for the items.

Sub Filter_ItemListInCode()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Array("Adams", "Baker", "Clark")
End Sub

The second example gets the Item list from a named range.

Sub Filter_ItemListInRange()
    Filter_PivotField _
        pvtField:=Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("SalesRep"), _
        varItemList:=Application.Transpose(Sheets("Sheet2").Range("SalesRepsToShow"))
End Sub

A Dynamic Range is the most flexible if you are going to have a variable number of Sales Reps that you want to display. In order to work with this code the Dynamic Range has to be non-volatile.

Hope this helps!

 

[sulley333]

My data would be located in range H13:P13...would I need to transpose? Do I need to name the range or can I just plug this range in somehow?

 

[Jerry Sullivan]

My data would be located in range H13:P13...would I need to transpose? Do I need to name the range or can I just plug this range in somehow?

In the second code example, you could replace SalesRepsToShow with your range address. Using a named range allows you to change the size of the range without going back into the VBA code...but if your range isn't going to change you can put the address into the code.

 

[sulley333]

Sorry...my second question...where to I place this code? In my code that builds the pivot table?

 

[sulley333]

what if the range won't always be populated...it is output of a multiple list box. There could be 1 to 9 cells filled.

 

[Jerry Sullivan]

Sorry...my second question...where to I place this code? In my code that builds the pivot table?

Here are some instructions on using VBA code...

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code)

1. Copy the code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

 

[sulley333]

Sorry...I am not asking very good questions...I have a module that has a sub routine that creates a pivot table that would use these fields...I am just not sure where to place the code...maybe it would be better if I showed you.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Major Caption")
        .Orientation = xlPageField
        .Position = 1
        .CurrentPage = caption
    End With

right now this is what I use to filter based on one value that they input called caption...not sure if I can use the code you have above to use more than one value.

 

[Jerry Sullivan]

what if the range won't always be populated...it is output of a multiple list box. There could be 1 to 9 cells filled.

That is why I suggested using a Non-Volatlle Dynamic Range to the Original Poster.

I'd suggest that you first get this to work with a fixed range...then make it work with a named range and lastly make it work with a Dynamic Range.

If you haven't used Dynamic Ranges, you can do a Google search to find instructions on how to do that. (Contextures.com and Ozgrid.com have some good examples). Since it is really a separate (and more complex) topic than this thread, you should probably start a new thread if you have questions on how to do that.

 

[sulley333]

Sorry...I will just ask one more question and if I am bothering you I can start a new thread!! It looks like it is working as far as selecting the fields I have listed...my pivot table is just not recognizing it. I am not sure where to place the code when creating the pivot table. Is the output a variable? Right now I have added it as follows:

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Major Caption")
        .Orientation = xlPageField
        .Position = 1
    Filter_PivotField _
        pvtField:=Sheets("Dashboard Data").PivotTables("PivotTable1").PivotFields("Major Caption"), _
        varItemList:=Array("Wife", "Mother")
    End With

Do you see where I am going wrong?