Excel 2013 Macro to set a filter

Recently I’ve been updated to Office 2013. Everything looks like is working smooth as always and even now Excel 2013 has a nice smooth effect when new data is displayed.

While I was working on a new report I had a reencounter with an old friend (or foe)… the Excel Macro!

A while ago, I wrote a short macro to set up the month and year filters every time the Excel file is opened, the idea is to set the filters to the current year and month. The good news are that this time I’ve created a new version of the filter in less than 1 hour. Actually in just 5 minutes! I caaaaaaaaan’t believe it…

Maybe is Excel 2013, maybe is my vast knowledge of Excel and VBA internals (joke), or even is just luck… but here is the script:

Sub CurrentMonth() 
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Purchase Document Dates].[PD Date Year].[PD Date Year]").ClearAllFilters 
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Purchase Document Dates].[PD Date Year].[PD Date Year]"). _     
    CurrentPageName = "[Dim Purchase Document Dates].[PD Date Year].&[" & Trim(Str(Year(Date))) & "]"
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Purchase Document Dates].[PD Date Period].[PD Date Period]").ClearAllFilters 
ActiveSheet.PivotTables("PivotTable1").PivotFields("[Dim Purchase Document Dates].[PD Date Period].[PD Date Period]"). _     
    CurrentPageName = "[Dim Purchase Document Dates].[PD Date Period].&[" & Trim(Str(Month(Date))) & "]"
End Sub
About these ads
Tagged with: ,
Posted in 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 20 other followers

Categories
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: