Macro to Filter Items in a Pivot Table

[JoeF]

I'm trying to write a macro that filters a record in a pivot table. The only way i can figre to do it is to list each and every item and make their visibility false. This would be o, if the data wasn't always changing. I would need to change the macro code every time new records are added to the data. Does anyone know a code that will let me hide all the records except the one that I specify as "true"?

Sub Macro6()
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer")
.PivotItems("1107469").Visible = True
.PivotItems("1112416").Visible = False
.PivotItems("1115176").Visible = False
.PivotItems("1136275").Visible = False

End With
End Sub

The other part of this question is why isn't this code working to select the active cell for the filter. The EXACT same code works in other workbooks, but i get an error on this pivot table. Code is the same exact, could it be something with the way the pivot table itself is set up? It fails on the ".pivotitems" line. I made sure the active cell picked up in "REFER_Filter" is a valid record in that pivot field, so that isn't the problem.

Sub Macro8()
REFER_Filter = ActiveCell.Value
With ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer")
.PivotItems(REFER_Filter).Visible = True
End With
End Sub

Thanks,
Joe

 

[SkipVought]

Hi,

A basic issue: you cannot have no pivot items selected. (sorry for the double negative)
So always start by having ALL pivot items selected.

You do this by looping thru the PivotItems collection...

dim pvi as pivotitem
for each pvi in  ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer").pivotitems
  pvi.visible = true
next

then loop, looking for the item you want to DISPLAY

for each pvi in  ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer").pivotitems
  if pvi.value <> SelectedValue then
     pvi.visible = false
  end if
next

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[JoeF]

Hey Skip,

That worked great. My only issue is that there are a lot of records and the pivot table actually changes for each record that gets a true or false argument, instead of applying them all at once. This takes some time. I cut the time in half though by using ClearAllFilters instead of setting the value to true for all the items in the first step. The macro now will take 10 or 20 seconds, but it's much better than hunting for the record manually in the filter list without a macro! If you have any further ideas on how to shave some time, let me know. Below is the current code. Thanks again!

Sub Tektips1modified()

Dim ReferToFilter
ReferToFilter = ActiveCell.Value

Dim pvi As PivotItem

ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer").ClearAllFilters

For Each pvi In ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer").PivotItems
If pvi.Value <> ReferToFilter Then
pvi.Visible = False
End If
Next

End Sub

 

[JoeF]

Actually, one of the feilds that I want to filter has hundreds of records in it and takes over 60 seconds to run. If i use it 30 times, I have waited close to an hour combined for the macros to run. If you have any quicker solutions for that, I'm all ears. The previous solution is perfect for 90% of what I need though, so thanks again.

 

[SkipVought]

Set up a LIST is the items you want to select. Easiest to NAME the list range -- something like SelectionList

Then run a loop to test for items in the list...

dim r as range, bFound as boolean
for each pvi in  ActiveSheet.PivotTables("PivotTable7").PivotFields("Refer").pivotitems
  bfound = false
  for each r in [SelectionList]
    if pvi.value = r.Value then
       bfound = true
       exit for
    end if
  next
  pvi.visible = bfound
next

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[JoeF]

I don't think i was clear on what the issue is. It's not that i have a list of items i need to filter all at once. The problem is that the macro takes a very long time to filter just one record. This is because the data array upon which my pivot table is based, has thousands of diffrent records. The pivot table updates after each record is hidden, so I sit there and watch the pivot table update and shrink as it is filtering out the unwanted records.

 

[SkipVought]

Then you can do it manually alot faster, using 3 mouse clicks and a scroll.
[tt]
1. select PivotField drop down
2. unselect Show All
3. scroll to item
4. select item
[/tt]
also, try putting this statement first in your macro

application.screenupdating = false

and this one LAST

application.screenupdating = true

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[JoeF]

Unfortunately,the screenupdateing code didn't do anything in this case. If it was fast to do it manually, i wouldn't have bothered with the macro in the first place... unfortunatley, it isn't. I have lists of thousands of records to scroll through and many of the records begin with numerics that have the same first 5 or 6 characters, so scrolling through to find the exact one you need to filter is a bit of a pain in the behind.