[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

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!
0
j2jake
Asked:
j2jake
  • 4
  • 4
1 Solution
 
j2jakeAuthor Commented:
forgot to upload the sample file. here it is.
test.xls
0
 
ghoshsaikat83Commented:
Find the attached file...

Joydip
sample.xls
0
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
ghoshsaikat83Commented:
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:
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now