Pivottable filter

[Looping through]

Is there a way to filter a pivot table list based on a cell range within the
sheet?

This is what I have

Sub Macro12()
With ActiveSheet.PivotTables("WON").PivotFields("Month Won")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = True
.PivotItems("(blank)").Visible = False
End With
End Sub

In this paticular case I am hiding all the options except "12", which
happens to correspond with the current month we are in. Can I just have the
pivot table look at cell "A2" and filter out all the extra criteria in that
column except what is specified in cell "A2"?

Any help is greatly apprechiated.
Peter

 

[Mike H.]

Is there a way to filter a pivot table list based on a cell range within the
sheet?

This is what I have

Sub Macro12()
With ActiveSheet.PivotTables("WON").PivotFields("Month Won")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = True
.PivotItems("(blank)").Visible = False
End With
End Sub

In this paticular case I am hiding all the options except "12", which
happens to correspond with the current month we are in. Can I just have the
pivot table look at cell "A2" and filter out all the extra criteria in that
column except what is specified in cell "A2"?

Any help is greatly apprechiated.
Peter

 

[Mike H.]

You pretty much have it. Just turn them all off and then turn on the one you
want:
I keep month 1 on until I turn all off then turn the "onemo" on and then
turn 1 off if it is not it because you can't filter out ALL of them and then
add back 1. You have to leave one and on.



Sub Macro12()
Dim OneMo as String
let OneMo=str(cells(1,2).value)
With ActiveSheet.PivotTables("WON").PivotFields("Month Won")
.PivotItems("1").Visible = true
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = false
.PivotItems("(blank)").Visible = False
.PivotItems(OneMO).Visible = True
if onemo<>"1" then
.PivotItems("1").Visible = false
end if
End With
End Sub

 

[Looping through]

Thanks Mike, But I want to select a certain month via VBA Code. My boss may
want to look at only month 3 or 12 and the pivot table populates
aoutmatically. I was hoping I could specify a date and run the macro to
filter all at once?

the reason I don't want to do this filtering after the fact is because I
have other information that populates under the table and once the table is
filtered from 500 rows to 20 the info below does not drag up with it.

Any suggestions.
Peter W

 

[Mike H.]

So have the macro run when the contents of the cell a2 changes. Also have
the macro hide "extra" rows so you don't have your other problem.

 

[Looping through]

yeap, that what I want to do. Now I just have to figure out how.... Any
thoughts.

Peter

 

[Mike H.]

In the Macro Editor, select the microsoft excel objects in the browser-like
area on the left, and then select This Workbook in the area under that. Then
create a sub:

Public CellVal as Double

Private Sub Workbook_Open()
sheets("sheet1").select
let CellVal=cells(1,2).value


In that sub, assign a public variable, like CellVal above to be the value of
a2 (may need more code if there are other tabs they may be on).
Next, on a sub in the sheet you're working with, put this:

In the example above, this would go on Sheet1:

Private Sub Worksheet_Calculate()

and in this sub:
if CellVal<>cells(1,2).value then
CellVal=cells(1,2).value 'reset for next time!
call yourmacro
end if



HTH!