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]

Ended up finding this: http://code.msdn.microsoft.com/CellFilterPivot
Which is to an extent helpful but for some reason when I try to implement it, it will not work.

 

[dylans]

Still no luck on this one

 

[dylans]

http://code.msdn.microsoft.com/CellFilterPivot
This code is actually pretty useful, you apply it to a specific cell, and it will take that value and use it as a filter. The only problem is you can only filter off of one value, and not multiple

 

[Andrew Poulsom]

What version of Excel do you have? Can you record a macro while filtering for a couple of items in the page area and post the code you get?

 

[dylans]

I am using Excel 2007.

  ActiveSheet.ChartObjects("Chart 11").Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Server").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Server")
        .PivotItems("server063").Visible = True
        .PivotItems("server059").Visible = True
        .PivotItems("server001").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Server").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Server")
        .PivotItems("server413").Visible = True
        .PivotItems("server431").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Server").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Server")
        .PivotItems("server001").Visible = False
        .PivotItems("server413").Visible = False
        .PivotItems("server431").Visible = False
    End With
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Server").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Server")
        .PivotItems("server063").Visible = False
        .PivotItems("server059").Visible = False
    End With

This is the code I get. Really curious if there is an easy way to just make the PivotItems to be based off a range of cells. It's been a long last week
Thanks for the reply Andrew!

 

[Andrew Poulsom]

Try the untested:

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

 

[dylans]

Andrew, the above works really well! I couldn't wrap my head around how to make it loop like that. That's exactly what I needed, I can't thank you enough!

Now I have a really complicated task to accomplish, I'll try to explain it in the least complicated way possible!
The selected field in A1 is different than the ones in A2:A10.
I have a chart showing the data for all of A1:A10 (assuming now that the cell is not blank).

I have the following formula I need to use:
NewCalculatedField = (A2:A10 CPU%)*(A2:A10 CPUCores / A1 CPUCores)

I have no idea if there is any way to do something this specific, I know you can create calculated fields, but I really don't think there is a way to do what I want in those; I am starting to hit a point where I think I should've done this in access

 

[Andrew Poulsom]

I think you will have to add the calculation to your source data and then put it in the pivot table.

 

[dylans]

You're right, and that can be done simply with an IF statement. Thanks for your help with this once again, I really appreciate it! You made everything work great!