change pivot table filter

Hi Experts!

Newbie here in excel VBA. I need to change the pivot table filter when the user chooses which month to display. It should show the trailing 4 months from the chosen month and year and the last year of the chosen month. I have uploaded a sample data.

In the selection sheet, "November" and "2009" is chosen so the pivot table shows 6 month filters. They are "2008-11", "2009-07","2009-08","2009-09","2009-10" and "2009-11".

Thanks so much!
j2jakeAsked:
Who is Participating?
 
ghoshsaikat83Connect With a Mentor Commented:
The formula in column G ("Month Filter" in sheet "Raw") has got the reference of "trailing months" which is an input field in the selection sheet. Buy changing the input in that field, the report is changed. You will have to refresh the report though. I am not that good in vba.....but I believe you have pretty much got what you wanted...didn't you...:)
0
 
j2jakeAuthor Commented:
forgot to upload the sample file. here it is.
test.xls
0
 
ghoshsaikat83Commented:
Find the attached file...

Joydip
sample.xls
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
ghoshsaikat83Commented:
Just change the selection in the Selection sheet and refresh the pivot table.

If you want the no of trailing to be dynamic instead of fixed as 4, that can also be done.

Regards,
Joydip
0
 
j2jakeAuthor Commented:
hi joydip!

thanks so much!

can you explain to me a bit of how this works?

how can you set the trailing to be fixed as 4 or to any number?
0
 
j2jakeAuthor Commented:
hi again!

i got the logic. thanks!

how can you set it up to be fixed as 4?

is there another way to like change the pivot filter using vba? what i really wanted to do is to only have the user interaction through the selection sheet. so after choosing the month and year, the pivot automatically changes without it being refreshed.
0
 
ghoshsaikat83Commented:
Dear i2jake,
>how can you set it up to be fixed as 4?

Find the attached file...

Joydip
sample.xls
0
 
j2jakeAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.