VBA refresh all pivot tables in the workbook then select the current month

Hi I'm new to this forum.


In the attached file, there is tab that is called the "destination" and "destination2" sheet tab. This is basically, where all the pivots are connected to the charts with slicers( sorry, for some reason I wasn't able to copy and paste the slicer on the destination tab so screenshot it to make sure you get the idea behind it). Also, The data will be added to each "source" tab daily...here what I want in the vba code which didn't work for me.


1. I want to make sure that each time the data is added to the source and source2 tab, that all the pivot tables are refreshed automatically with click button on each sheet with current month selected in the pivot table and filters out the other months

Hi mz_h

Does the below macro do what you're looking for ?

Please Login or Register  to view this content.

HI Nankw83,

I have a question about the code that you sent above. First of all, thank you for a quick response.

1. My first question is if the "months" has sometimes a different field name...just like the multiple sheets names differ..for example some fields name might be "calldate(month)","months" and when I added these in the code it gave me an error (see below). Can I use a command button or it needs to be in module since when I used the command button it gave me an error? The file is attached

With Pvt.PivotFields("Months","Calldate(month)")
.ClearAllFilters

2. My second question is I have two specific pivot tables that has three months selected and want it to roll 3months at a time with VBA after I refresh with the above code? Can you please assist so basically, whenever the data source is updated the months in the pivot will show only 3 months including the current month.


Capture_1.PNG

HOW TO ATTACH YOUR SAMPLE WORKBOOK:

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Hi mz_h,

Apologies I couldn't reply back earlier ... Try the below code & I have also attached the sample file I worked on

Please Login or Register  to view this content.

Hi mz_h,

If you are getting a message "We found a problem with some content in [file name]. Do you want us to recover as much as we can ... etc.". Click 'Yes' then you'd probably get another message "Microsoft Excel was attempting to open & repair the file ... etc." & then the file still won't open !

Once above process is done, go to your downloads folder, right click on the file then click properties & check the last checkbox to "Unblock" where it says "Security: This file came from another computer and might be blocked to help protect this computer"

I have encountered this issue suddenly yesterday where any downloaded file from the forum won't open & after googling I found this work around. I am still searching why this has happened suddenly & how to prevent it

Can't see the attached image ... The codes loops 4 times, hence For x = 1 to 4 and the below block of code indicates what to do i.e. 1 month or 3 months. So for the first & third loop, it performs the first part of "IF" condition 2 & 4 performs the second part, i.e. 3 months

Please Login or Register  to view this content.