- User: Can we make this month filter to have by default the year to date months selected?
- Me: … well… I guess I can have a look at it
- Inside of me: Back to VBA… after 12 years…
So I struggled a bit, with errors 13, 1004… Search on internet and you will find long solutions (if you are lucky) to fix those errors and maybe none like you scenario. Well for me it turned out that a bit of debuging lighted up the bulb and I spotted the source of the 1004 error… just a little and simple Trim() function. The code talks by itself (How much I missed ; ) :
Note: Save first your Excel file as a Macro Enabled file (.xlsm).
Sub Auto_Open() YTDMonth End Sub Sub YTDMonth() Dim i As Integer Dim m As Integer Dim months() As Variant Dim years(1) As Variant i = 1 m = Month(Date) ReDim strArray(1 To m) For i = 1 To m months(i) = "[Dim Period].[Period Name].&[" & Trim(Str(i)) & "]" Next i years(1) = "[Dim Year].[Year].&[" & Trim(Str(Year(Date))) & "]" ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Year].[Year].[Year]").VisibleItemsList = years() ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Period].[Period Name].[Period Name]").VisibleItemsList = months() End Sub