Pivot Table Filtering - Macro

[kelly gardner]

Hi,
I am trying to filter a pivot table through a macro and what I have works but I have come to find out that the source data is more dynamic than I thought and is breaking my macro each month I run it. Here is a small exerpt of what I have so far:

ActiveSheet.PivotTables("PivotTable").PivotFields("Project ID").CurrentPage = _"(All)"

'Filter for KT Hours
With ActiveSheet.PivotTables("PivotTable").PivotFields("Project ID")
.PivotItems("7012276").Visible = False
.PivotItems("7012279").Visible = False
.PivotItems("2012314").Visible = False
.PivotItems("5012315").Visible = False
.PivotItems("4012335").Visible = False
.PivotItems("2174974").Visible = True
.PivotItems("1174975").Visible = True
.PivotItems("4012323").Visible = False
.PivotItems("4012433").Visible = False
.PivotItems("4013422").Visible = False

 

[Barb Reinhardt]

I think I'd do something like this

'Unfilter them all
Dim myPivotTable as Excel.PivotTable
Dim myPivotField as Excel.PivotField
Dim myPivotItem as Excel.PivotItem

Set myPivotTable =ActiveSheet.PivotTables("PivotTable")
Set myPivotField = myPivotTable.PivotFields("Project ID")

for each myPivotItem in mypivottable.pivotitems
if not myPivotItem.visible then
myPivotITem.visible = true
end if
next myPivotItem

with mypivottable

.pivotitem("2174974").visible = false
.pivotitem( "1174975").visible= false

end with


Note this is UNTESTED.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Hide/Show PivotItems with macro.
With dates instead of numbers.
PivotChart labeled with visible items.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_05_10.xlsm
Pdf preview:
http://www.mediafire.com/file/mjddomtjmnz/04_05_10.pdf
Note: This has been Tested.

 

[Kel]

I think this is close only I am getting an error on the 'For' Statement. Do
you need to set the myPivotItem to be equal to something?
It is frustrating because there has got to be a way to just ask the macro to
return the values under those two codes. ARGH!
Thanks for the effort