Filtering active pivot table fields

Tanner_2004

Well-known Member
Joined
Jun 1, 2010
Messages
616
Is there a way to edit this code so that I it runs on the active sheet which is store the pivot table?

Thank you for reading and any help.

Code:
[COLOR=#000000][FONT=&quot][B]Sub PivotTableFilter10()[/B][/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("Sheet1").PivotTables("PivotTable1")

PvtTbl.PivotFields("Region").PivotItems("Europe").Visible = True[/FONT][/COLOR]
[COLOR=#000000][FONT=&quot]
[B]End Sub[/B][/FONT][/COLOR]
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
To refer to the active sheet, try...

Code:
Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")

If you want to test for the existence of the pivot table, try...

Code:
On Error Resume Next
Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")
On Error GoTo 0

If Not PvtTbl Is Nothing Then
    PvtTbl.PivotFields("Region").PivotItems("Europe").Visible = True
Else
    MsgBox "Pivot table not found.", vbInformation
End If

Hope this helps!
 
Upvote 0
Thank you..you have helped me before I believe. I am not having trouble finding a pivot table; I am looking for a way to avoid naming the worksheet and the pivot table. I simply want to run this on the active worksheet that contains the pivot table.
It also appears that selecting the items you want to make visible involves hiding all of the other items that you want to hide. Is that correct?

I wonder if I select none, then just the items that I want will work. What do you think?

Thank you.
 
Upvote 0
In that case, you can refer to the pivot table by index number, instead of name...

Code:
Set PvtTbl = ActiveSheet.PivotTables(1)

Yes, to select one or more items, you'll need to first clear the filter so that all are selected/visible, and then you'll need to hide the ones you don't want by setting the Visible property for each pivot item to False.
 
Last edited:
Upvote 0
Thank you, but I just found this on the forum. I will add your code to it. Thank you.
[h=2]
icon1.png
Re: Hide all pivotitems except 2 w-VBA[/h]

quote_icon.png
Originally Posted by Andrew Poulsom
Try:

For Each oPI In ActiveSheet.PivotTables("PivotTable2").PivotFields("User").PivotItems



worked perfectly...thx, Andrew!​
 
Upvote 0
Also, don't forget to clear the filter for your pivot field first. So you can do something like this...

Code:
Dim oPI As PivotItem

With ActiveSheet.PivotTables("PivotTable2").PivotFields("User")
    .ClearAllFilters
    For Each oPI In .PivotItems
        'etc
        '
        '
    Next oPI
End With
 
Upvote 0
You read my mind and current challenge. Here is my code:

Where do I add/insert: .ClearAllFilters

Code:
Sub showpivotitems()


    Dim oPI As PivotItem
    
        
    For Each oPI In ActiveSheet.PivotTables(1).PivotFields("WorkingPositionOwner").PivotItems
  
    
        Select Case oPI.Name
      
            Case "Billie Mosley", "Cathy", "Cole", "Eddie", "Diane", _
            "Elizabeth", "Hernandez", "Parker"
            
            Case Else
              oPI.Visible = False
        End Select
    Next oPI
    
End Sub
 
Upvote 0
You can either use a separate line, like this...

Code:
Sub showpivotitems()


    Dim oPI As PivotItem
    
    [COLOR=#ff0000]ActiveSheet.PivotTables(1).PivotFields("WorkingPositionOwner").ClearAllFilters[/COLOR]
    
        
    For Each oPI In ActiveSheet.PivotTables(1).PivotFields("WorkingPositionOwner").PivotItems
  
    
        Select Case oPI.Name
      
            Case "Billie Mosley", "Cathy", "Cole", "Eddie", "Diane", _
            "Elizabeth", "Hernandez", "Parker"
            
            Case Else
              oPI.Visible = False
        End Select
    Next oPI
    
End Sub

Or, you can use a With statement, as per my previous post...

Code:
Sub showpivotitems()

    Dim oPI As PivotItem
    
    [COLOR=#ff0000]With ActiveSheet.PivotTables(1).PivotFields("WorkingPositionOwner")
        .ClearAllFilters[/COLOR]
        For Each oPI In [COLOR=#ff0000].PivotItems[/COLOR]
            Select Case oPI.Name
          
                Case "Billie Mosley", "Cathy", "Cole", "Eddie", "Diane", _
                "Elizabeth", "Hernandez", "Parker"
                
                Case Else
                  oPI.Visible = False
            End Select
        Next oPI
    [COLOR=#ff0000]End With[/COLOR]
    
End Sub
 
Last edited:
Upvote 0
Please forgive the delayed expression of gratitude. Thank you; I am looking forward to giving it a try.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top