excel - VBA to change Pivot Filter in OLAP cube to a range

Question

I have a pivot table that pulls data from an OLAP cube, and I'd like to create a macro to filter a pivot field that contains 'Week of Year' based on the value in another cell, so that I can easily change the time frame of the table.

My experience with cube sets is very limited, so I used the macro recorder to see what was happening. The sample code I got was:

ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Time].[Week of Year].[Week of Year]").VisibleItemsList = Array( _
        "[Time].[Week of Year].&[1]", "[Time].[Week of Year].&[2]", _
        "[Time].[Week of Year].&[3]")

Is there a way to simplify this so that it sets the filter with the array 1 to n, with n being value of another cell? My aim to to be able to show weeks 1 through 15, by entering 15 in a specified field.

Answer 1

It should be something like this:

Dim aWeeks()
Dim n                     As Long
Dim x                     As Long

n = Range("A1").Value

ReDim aWeeks(n - 1)
For x = 1 To n
    aWeeks(x - 1) = "[Time].[Week of Year].&[" & x & "]"
Next x
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Time].[Week of Year].[Week of Year]").VisibleItemsList = aWeeks