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

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.
--
HTH,

Barb Reinhardt



"kelly gardner" wrote:

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
.
.
End With
ActiveSheet.PivotTables("PivotTable").PivotFields( "Project ID"). _
EnableMultiplePageItems = True

There are about 150 different "numbers" that are in the "Project ID." I only need to filter by two of them at different times. One with just those numbers and one with everything but those numbers. Is there a way to clean up this coding mess and just list those two numbers that I need or do I need to list EVERY number in the pivot table and mark it as "True" or "False?"

Cleaning it up would help the macro run much faster and keep me from ahving to update it each time I need to run a report.
Thanks,
Kel


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Writing and using a custom referenced functoid.
http://www.eggheadcafe.com/tutorials...-and-usin.aspx
.

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

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

"Barb Reinhardt" wrote:

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.
--
HTH,

Barb Reinhardt



"kelly gardner" wrote:

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
.
.
End With
ActiveSheet.PivotTables("PivotTable").PivotFields( "Project ID"). _
EnableMultiplePageItems = True

There are about 150 different "numbers" that are in the "Project ID." I only need to filter by two of them at different times. One with just those numbers and one with everything but those numbers. Is there a way to clean up this coding mess and just list those two numbers that I need or do I need to list EVERY number in the pivot table and mark it as "True" or "False?"

Cleaning it up would help the macro run much faster and keep me from ahving to update it each time I need to run a report.
Thanks,
Kel


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk: Writing and using a custom referenced functoid.
http://www.eggheadcafe.com/tutorials...-and-usin.aspx
.