Pivot Table Filtering - Macro

  • Thread starter Thread starter kelly gardner
  • Start date Start date
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
 
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.
 
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
 
Back
Top