Pivot Table Filtering - Macro

K

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
 
B

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.
 
K

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top