Code to hide pre/post date ranges in pivot tbl in 2003 fails in 2010

[Cyclops]

The following code works fine in excel2003 but fails in 2010.

Please Login or Register  to view this content.

The funny thing is that if I record a macro in 2010, that's the exact syntax I get and it won't run!!?!?

The pivot table is grouped by year and month. When I set the rangeto only show a certain date range, it adds the items from before and after the range I have selected. I don't want those so I need to get rid of them. The code works fine in 2003 but not in 2010.

Here's a sample file. It works in 2003 but not 2010.

Testing Pivots.xls

[Cyclops]

I've made some small progress here but I'm still rather confused.

Please Login or Register  to view this content.

Will fail. Assuming it's the first item, the following will work to return the pivotitem.

Please Login or Register  to view this content.

Unfortunately, adding .visible = false (as below) fails.

Please Login or Register  to view this content.

The same code does work for standard "year" items so it seems that it won't allow hiding of the "before and after" items. Maybe there's another way. I set the range by right clicking and selecting group. I don't know why those even show up on there in the first place as I've clearly set the grouping range to not include dates before or after.

I'm hoping that there's some easy setting that I'm missing to simply not show those.

[Cyclops]

I never did figure this out. I need to go in and manually unselect the pre and post date ranges. It kind of sucks because someone else updates the file and it's all automatic except for disabling the pre and post date ranges.

I haven't played with it in a while but I think I did about a year ago with no luck.

[Cyclops]

I thought I found a solution but it seems I was confused. Had a similar issue on a pivot table. I was able to use autofilter to set a date range.

Please Login or Register  to view this content.

That worked for the new pivot table in a new project. I tried on old, problem pivots and it errored out. It seems that I can't use autofilter on those pivot tables. Even outside of VBA, the icon is grayed out when I select the date column.

Would appreciate any thoughts on this. I suppose the next thing to try would be to remove the date groupings from the pivots, filter the dates then add the groupings back. No time now but will try that next week.