+ Reply to Thread
Results 1 to 6 of 6

VBA to control filter date in multiple pivot tables

  1. #1
    Registered User
    Join Date
    05-28-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    7

    Angry VBA to control filter date in multiple pivot tables

    Hi All,

    I am fairly new to VBA so if you could bare with me it would be much appreciated.

    What I would like to achieve is the ability to change the date filter in a whole range of pivot tables in multiple different worksheets in increments of months which take the form of "mmm-yy" EG. APR-15 or May-15. I have around 25 pivot tables and it is fairly time consuming moving between tabs changing the dates at the end of the month.

    I have looked for this previously and seen that slicers are an option - but my data does not all come from the same source so as far as I am aware it doesn't work.

    Could someone please help me to start to write some form of Macro that changes all of the dates of the pivot tables in all of the worksheets but 1 month.

    Or even better could I create a tab of which you type the desired month of choice in the same format (mmm-yy) as the table and this will then run the macro by pressing a button. This will in turn update all the tables to the desired month. Is this possible? Plus how will this be affected in years going forwards?

    Any assistance would be very much appreciated.

    Many thanks
    Harvey

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA to control filter date in multiple pivot tables

    its plausible

    see attached example
    Please Login or Register  to view this content.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA to control filter date in multiple pivot tables

    the I and intPI are control mechanisms for filtering
    as you cannot filter nothing (it will crash your code if you do)

    if by the last item nothing has been filtered....all filters are cleared

    this is a control environment though...if filters aren't working its probably due to the "filter" variable not working as intended

  4. #4
    Registered User
    Join Date
    05-28-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA to control filter date in multiple pivot tables

    Hi Hum,

    I have attempted your Macro in my file and it seemed to do absolutely nothing.

    I am fairly new to this and don't fully understand your second post in this thread - if you could please assist me in getting to the bottom of how to get this to work it would be much appreciated.

    Many thanks
    Harvey

  5. #5
    Registered User
    Join Date
    05-28-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    7

    Re: VBA to control filter date in multiple pivot tables

    I have opened your excel file and had a look at that - sorry I didn't fully explain this in my explanation the month is the report filter of all of the pivot tables. But there are also two report filters on the pivots, the first report filter is 'Business Unit' which I suppose from your example would be region and we have a pivot for each 'Business Unit' or 'Region' then the second report filter is month, the business unit will never change and is just from the initial set up of the file. The month is the report filter than needs to change every month. Apologies for the confusion.

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: VBA to control filter date in multiple pivot tables

    Hi Harvey

    Note the reason why i said it was plausible not definite
    i created that sample in order to show it how it was plausible
    without something to work on its impossible to tell as VBA pivot tables is finicky

    Breaking down what my code does
    there is a loop that loops every pivot table
    within that loop another loop that goes thru all the pivot table field
    checks the filter value to see if pivot table field exist in the pivot table
    if it doesn't exist...hide that value
    goes on hiding all lines and if it reaches the last line and doesnt find anything....clears all filters (this is what i am talking about in the 2nd post)

    anyway i digress,
    what i do mean to say is that this code is tailor made specific
    you cant just plonk it in anywhere and hope it works

    so unless you have a example sheet i can work with....i wouldn't know how to tell you know to fix it

    ie
    Attach a sample workbook. Remember to desensitize the data.
    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    View Pic

    i stress the bold bit
    Last edited by humdingaling; 06-03-2015 at 01:31 AM. Reason: wording

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. One filter/ Master filter multiple pivot tables based on worksheet...
    By jlworden in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-10-2013, 11:16 AM
  2. Duplicate Report Filter and Row Labels Filter Across Multiple Pivot Tables
    By tash in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-25-2013, 05:32 PM
  3. Filter several pivot tables by one programmed date range filter in Excel 2003
    By olewka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-08-2011, 11:49 AM
  4. Multiple pivot tables 1 filter to control all
    By Cjax in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-11-2011, 02:52 PM
  5. Replies: 3
    Last Post: 06-23-2009, 01:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1