Filter values in pivot table by reference to a range

D

dylans

New Member
Joined
Jun 18, 2010
Messages
12
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:
Code: 
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
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?
 
Upvote 0
I am using Excel 2007.

Code: 
  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 :laugh:
Thanks for the reply Andrew!
 
Upvote 0
Try the untested:

Code: 
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
 
Upvote 0
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 :laugh:
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Filtering multiple pivottables from a cell value
Replies
2
Views
171
Joe4
Joe4
M
  • Solved
Loop through unfiltered Pivot Table items
Replies
1
Views
315
mikeymay
M
ghull99
  • Question Question
Attempting VBA to refresh "Chart Title" based on Pivot Filters. Filter1 works in VBA, but title doesn't update based on filter2.
Replies
0
Views
72
ghull99
ghull99
H
  • Solved
Macro to Filter Pivot Table
Replies
2
Views
845
howard
H
M
  • Question Question
Pivot Tables & Dates
Replies
0
Views
44
mikeymay
M

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

Share this page

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