Filter values in pivot table by reference to a range

[dylans]

The title says it all; after doing much reading and referencing unhelpful MSDN posts, I came to a dead end.

I have cells A1:A10 which have certain values entered in manually.

I have a pivot table with data similar to the data entered in manually for cells A1:A10.

I want the pivot items in my pivot table to show only the items that are in the range A1:10, and hide items that were not found.

This is what I've come up with so far:

Sub PT()
'
'PT Macro
'
Dim PT As PivotTable
Set PT = Sheets("Sheet3").PivotTables("PivotTable2")
PT.PivotFields("Server").CurrentPage = Sheets("Sheet2").Range("a1:a10").Value
Set PT = Nothing
End Sub

Unfortunately it will filter only on one cell within the range, not all 10.
Any help with this would be greatly appreciated!

-Dylan

 

[dylans]

What Andrew posted worked well, the downside is if I have 200 some entries in the pivot filter to go off of, naturally looping like that takes awhile. Out of curiosity, is there any any way to make it more efficient?

 

[Fazza]

Hi, dylans. This might help? Untested. F

Sub PT()
  '
  'PT Macro
  '
  Dim PT As PivotTable
  Dim PI As PivotItem
 
  Set PT = Sheets("Sheet3").PivotTables("PivotTable2")
  PT.PivotFields("Server").CurrentPage = "(All)"
 
  PT.ManualUpdate = True
  For Each PI In PT.PivotFields("Server").PivotItems
    PI.Visible = WorksheetFunction.CountIf(Sheets("Sheet2").Range("A1:A10"), PI.Name) > 0
  Next PI
  PT.ManualUpdate = False
  Set PT = Nothing
 
End Sub

 

[dylans]

Thanks for the suggestion, I had an error in my code but that fixed it

Still takes a bit to run through, maybe I'm too impatient

 

[Fazza]

The WorksheetFunction might be an execution speed bottleneck. Maybe instead load the values (in VBA) into an array and check against that array? Or maybe a collection?

Maybe even take a totally different approach. I can't be sure without some sample data. The idea being to join the two data sources - one the full data and the other the list of values to show - via SQL and feed that dataset into the pivot table. Maybe without VBA, depending on requirements.