Pivot Table filter using a MACRO

[MrPaul412]

Hi Everyone,

Is there a way of writing a macro to filter a Pivot Table that unselects all filter criterial and then only select what I require?

The Macro I have created, using the recorder, unticks all the names under the filter then ticks the relivant name to be filtered. This worked a treat until I've refreshed the data table and now I've ended up with more peoples names as I presume they where not Unselected in the first place (sorry for the explantion)

The code I've used is (and I apolligise for the laymans approach)-

Sub FilterStockPiv()
'
' FilterStockPiv Macro
' Macro recorded 02-05-2011 by u417939
'
'
Range("C12:L12").Select
Sheets("Piv Stock data").Select
Range("AI1").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MRP Controller2")
.PivotItems("Anderson, Stevie").Visible = False
.PivotItems("Ann, Liu").Visible = False
.PivotItems("Ashraf, Omar").Visible = False
.PivotItems("Bailey, John A").Visible = False
.PivotItems("Bamford, Jenny").Visible = False
.PivotItems("Baskcomb, Mark").Visible = False
.PivotItems("Bell, Ryan").Visible = False
.PivotItems("Berresford, Clive").Visible = False
.PivotItems("Betteridge, Stuart").Visible = False
.PivotItems("Bishop, Jack").Visible = False
.PivotItems("Bowman, Bradley Q").Visible = False
.PivotItems("Broadhurst, M").Visible = False
.PivotItems("Brook, Debra").Visible = False
.PivotItems("Bull, Chris").Visible = False
.PivotItems("Byrne, Dan").Visible = False
.PivotItems("Chen, Fanghui").Visible = False
.PivotItems("Chung Mo, Ahn").Visible = False
.PivotItems("Cooper, Vince").Visible = False
.PivotItems("Cox, Craig").Visible = False
.PivotItems("Daly, Charlene").Visible = False
.PivotItems("Darrington, David").Visible = False
.PivotItems("Degen, Erdmute").Visible = False
.PivotItems("Dillingham, Alan").Visible = False
.PivotItems("Duro, Chris").Visible = False
.PivotItems("Dyer, Rod").Visible = False
.PivotItems("Eddleman, Dave").Visible = False
.PivotItems("Ellaway, Kayleigh").Visible = False
.PivotItems("Exley, Steve").Visible = False
.PivotItems("Fleming, Tony").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MRP Controller2")
.PivotItems("Frank, Lu").Visible = False
.PivotItems("Frape, Simon J").Visible = False
.PivotItems("Gallagher, Steve").Visible = False
.PivotItems("Garrett, Gavin").Visible = False
.PivotItems("Gascoyne, Steve").Visible = False
.PivotItems("Gibson, Gerry").Visible = False
.PivotItems("Gori, Diane").Visible = False
.PivotItems("Gustafson, Andrew").Visible = False
.PivotItems("Hall, Mark").Visible = False
.PivotItems("Hammersley, Martin").Visible = False
.PivotItems("Harris, Jamie").Visible = False
.PivotItems("Harris, Steven").Visible = False
.PivotItems("Hsu, Sandy").Visible = False
.PivotItems("Hutson, Colin").Visible = False
.PivotItems("Huyser, Justin").Visible = False
.PivotItems("Jakeway, Lori A").Visible = False
.PivotItems("Kavanagh, Steph").Visible = False
.PivotItems("Krull, Volker").Visible = False
.PivotItems("Lacy, Rhonda").Visible = False
.PivotItems("Liu, Marco").Visible = False
.PivotItems("Lomas, Cheryl A").Visible = False
.PivotItems("Lowe, Chris").Visible = False
.PivotItems("Marazzi, Stefano").Visible = False
.PivotItems("Marco, Liu").Visible = False
.PivotItems("Maris, Suzanne E").Visible = False
.PivotItems("Martin, Scott").Visible = False
.PivotItems("Mather, Glenn").Visible = False
.PivotItems("Matthews, Tim").Visible = False
.PivotItems("McCorrie, Iain").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MRP Controller2")
.PivotItems("McMath, Colin").Visible = False
.PivotItems("Melzer, Andrea").Visible = False
.PivotItems("Miles, Adrian").Visible = False
.PivotItems("Nack, Thorsten").Visible = False
.PivotItems("Neale, Mike").Visible = False
.PivotItems("Ness, Pat").Visible = False
.PivotItems("Nicoletti, Eric").Visible = False
.PivotItems("O'Keeffe, Jim").Visible = False
.PivotItems("Parry, Lee").Visible = False
.PivotItems("Pitts, Simon M").Visible = False
.PivotItems("Platt, Wayne").Visible = False
.PivotItems("Pleass, Andrew").Visible = False
.PivotItems("Pleass, Andy").Visible = False
.PivotItems("Richards, Lorraine").Visible = False
.PivotItems("Robinson, Paul").Visible = False
.PivotItems("Rodman, Richard").Visible = False
.PivotItems("Saundh, Arvinder").Visible = False
.PivotItems("Sheldon, Simon").Visible = False
.PivotItems("Sidhu, Bob").Visible = False
.PivotItems("Singh, Charandeep").Visible = False
.PivotItems("Smale, Chuck").Visible = False
.PivotItems("Smith, Brian C").Visible = False
.PivotItems("Sparks, Dennis P").Visible = False
.PivotItems("Spicer, Mary M").Visible = False
.PivotItems("Spieweg, Bettina").Visible = False
.PivotItems("Spoerl, Leland").Visible = False
.PivotItems("Stacey, Rich").Visible = False
.PivotItems("Stephens, Andy").Visible = False
.PivotItems("Stone, Paul").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("MRP Controller2")
.PivotItems("Talwar, Reena").Visible = False
.PivotItems("Tan, Edel").Visible = False
.PivotItems("Thomson, Mark").Visible = False
.PivotItems("Towell, David").Visible = False
.PivotItems("Whitt, Mark").Visible = False
.PivotItems("Wildey, Kevin").Visible = False
.PivotItems("Wilson, Paul").Visible = False
.PivotItems("Wilson, Paul R").Visible = False
.PivotItems("Wood, Craig").Visible = False
.PivotItems("Woodward, Kevin").Visible = False
.PivotItems("Wright, Richard J").Visible = False
.PivotItems("Wynne, Paul").Visible = False
End With

Thanks for any help I receive!

Paul

 

[Jerry Sullivan]

Hi Paul,

You can use this code to deselect everything except 1 or more items you list in the Array:

Sub Filter_PivotTable_Using_Array()
    Dim varItemList() As Variant
    Dim strItem1 As String
    Dim i As Long
    On Error GoTo ErrorHandler:
    Application.ScreenUpdating = False
 
    varItemList = Array("San Diego", "Atlanta", "Boston")
    strItem1 = varItemList(LBound(varItemList))
    With Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("Location")
        .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 Sub
ErrorHandler:
    MsgBox "Error: " & Err.Description
End Sub

Is there a way of writing a macro to filter a Pivot Table that unselects all filter criterial and then only select what I require?

I'm not aware of any way to set all the items to Visible=False. Excel requires that at least one item be visible. To work within that constraint, the code above starts by setting the first item in the array list to Visible=True.

 

[MrPaul412]

Thanks for the reply Jerry.

I will try your solution and let you know how I get on.

Paul

 

[MrPaul412]

Hat off to you Jerry. Works lilke a dream.

Kind regards

Paul