Excel Macro to update filter

  • 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
Advertisements
Tagged with:
Posted in Errors, Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

Categories
%d bloggers like this: