OLAP Pivot Filter using VBA

Hi,

Probably a really obvious answer but I’m wondering if anyone can please assist. I have a OLAP Pivot table and need a Macro which only selects certain items, so far I have the below. I can get it to hide all the years which is all items but then I want it to only select the dates between 08/06/2014 – 12/06/2014. For some reason it doesn’t like the VisibleItemsList bit, what am I doing wrong? Eventually I want the dates that need to be taken from cells which someone inputs but for now I’m happy just to get it to filter by pre-determined dates in the code.

Sub PivotFilter()
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[PeriodYear]").HiddenItemsList = Array( _
"[Dates].[PeriodDates].[PeriodYear].&[2011]", _
"[Dates].[PeriodDates].[PeriodYear].&[2012]", _
"[Dates].[PeriodDates].[PeriodYear].&[2013]", _
"[Dates].[PeriodDates].[PeriodYear].&[2014]")

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[Day]").VisibleItemsList = Array( _
"[Dates].[PeriodDates].[Day].&[2014-06-08T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-09T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-10T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-11T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-12T00:00:00]")
End Sub

Thanks

Tom

Thanks for the reply Izandol.

I've tried not hiding 2014 but it's still crashing when it get to this bit:

ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[Day]").VisibleItemsList = Array( _
"[Dates].[PeriodDates].[Day].&[2014-06-08T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-09T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-10T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-11T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2014-06-12T00:00:00]")

I can get it to hide ever date apart from the ones I need but that means I have to define each date individually apart from the ones I need filtered. It’s really bugging me that if I can do that why can’t I just ask it to make them visible and define only the items I needI can get it to hide ever date apart from the ones I need but that means I have to define each date individually apart from the ones I need filtered. It’s really bugging me that if I can do that why can’t I just ask it to make them visible and define only the items I need.

I recorded a macro where I just select one day to see what it gives me and it's doing it the long\awkward way of asking for everything to be hidden but the item/date I have selected! The below code is simply me clicking on the filter and ticking one day.

ActiveSheet.PivotTables("PivotTable2").CubeFields(2).TreeviewControl.Drilled = _
Array(Array(""), Array("[Dates].[PeriodDates].[PeriodYear].&[2013]"), Array( _
"[Dates].[PeriodDates].[PeriodNo].&[255]"), Array( _
"[Dates].[PeriodDates].[PeriodWeek].&[2551]"))
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[PeriodYear]").HiddenItemsList = Array( _
"[Dates].[PeriodDates].[PeriodYear].&[2011]", _
"[Dates].[PeriodDates].[PeriodYear].&[2012]")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[PeriodNo]").HiddenItemsList = Array( _
"[Dates].[PeriodDates].[PeriodNo].&[253]", _
"[Dates].[PeriodDates].[PeriodNo].&[254]", _
"[Dates].[PeriodDates].[PeriodNo].&[256]", _
"[Dates].[PeriodDates].[PeriodNo].&[257]", _
"[Dates].[PeriodDates].[PeriodNo].&[258]", _
"[Dates].[PeriodDates].[PeriodNo].&[259]", _
"[Dates].[PeriodDates].[PeriodNo].&[260]", _
"[Dates].[PeriodDates].[PeriodNo].&[261]", _
"[Dates].[PeriodDates].[PeriodNo].&[262]", _
"[Dates].[PeriodDates].[PeriodNo].&[263]", _
"[Dates].[PeriodDates].[PeriodNo].&[264]")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[PeriodWeek]").HiddenItemsList = Array( _
"[Dates].[PeriodDates].[PeriodWeek].&[2552]", _
"[Dates].[PeriodDates].[PeriodWeek].&[2553]", _
"[Dates].[PeriodDates].[PeriodWeek].&[2554]", _
"[Dates].[PeriodDates].[PeriodWeek].&[2555]")
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"[Dates].[PeriodDates].[Day]").HiddenItemsList = Array( _
"[Dates].[PeriodDates].[Day].&[2013-02-24T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2013-02-26T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2013-02-27T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2013-02-28T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2013-03-01T00:00:00]", _
"[Dates].[PeriodDates].[Day].&[2013-03-02T00:00:00]")

It crashes out with the below message messages

Untitled.png