VBA Months in Pivot Table

Analyze_This

Board Regular
Joined
Oct 29, 2008
Messages
122
Hey guys! I'm having a problem writing code that will allow for a range of months in a pivot table. First, here's the code for Jan-2010 through June-2010:

Code:
ActiveSheet.PivotTables("PivotTable8").PivotFields( _
        "[Dim Month].[Month ID].[Month ID]").VisibleItemsList = Array( _
        "[Dim Month].[Month ID].&[201001]", "[Dim Month].[Month ID].&[201002]", _
        "[Dim Month].[Month ID].&[201003]", "[Dim Month].[Month ID].&[201004]", _
        "[Dim Month].[Month ID].&[201005]", "[Dim Month].[Month ID].&[201006]")

This is connected to a data cube and it's how I have to filter the months. So If I want to add a month, I'd need to add another "[Dim Month].[Month ID]&[YearMonth]".

Is there a way that I can automate this with VBA so that it filters month from Jan to Current Month minus 1?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Watch MrExcel Video

Forum statistics

Threads
1,118,048
Messages
5,569,879
Members
412,298
Latest member
dietitiann
Top