+ Reply to Thread
Results 1 to 12 of 12

Macro to select multiple pivot table filters, based on values in a table

  1. #1
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Macro to select multiple pivot table filters, based on values in a table

    Is this possible? Iīm looking for a macro that selects multiple filters on a pivot table, based on the values of a regular table.

    For example in C1:C10 I have a list of values: (1,2,3,4,5,a,b,c,d,e). The values and the range can change.
    I want to automatically select these values in a specific pivot table filter when I run the macro.

    Thanks

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Macro to select multiple pivot table filters, based on values in a table

    Use code like this: in this example, Sheet2 has the list of values, and the pivottable sheet name, name, and field name are all made up by me....

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Macro to select multiple pivot table filters, based on values in a table

    Hi

    Thanks for the answer and apologies for the delay. I only had some time to look at this now.
    I tried your code and made the name adaptations to fit a test sheet, but canīt seem to make it work.

    The macro is selecting some values on the filters, but not the ones in range C of sheet2 and then it ends with an error.
    Tried some variations without success

    Attached is the test sheet created
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Macro to select multiple pivot table filters, based on values in a table

    The issue is that I assumed that the pivot items would be strings, not numbers. If they will be numbers in the actual table, then change

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Macro to select multiple pivot table filters, based on values in a table

    Thank you! It works great when I have numbers only.

    The issue is that it does not work for alpha-numerical values.
    The pivot items can be both numeric and alfa-numeric (car models).

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Macro to select multiple pivot table filters, based on values in a table

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Macro to select multiple pivot table filters, based on values in a table

    Use a conditional in your Match:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Macro to select multiple pivot table filters, based on values in a table

    Hi Bernie, I really appreciate your help but I canīt seem to make it work and it is wrecking my head. Tried different variations without luck.
    I canīt see what I am doing wrong. See attached the sheet created. The macro “test” is assigned to the shape.

    Thanks!
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Macro to select multiple pivot table filters, based on values in a table

    The PI cache was out of date - you must have overwritten some values after creating the pivot table, so the table needed to be refreshed.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Macro to select multiple pivot table filters, based on values in a table

    This is getting strange... Sorry, itīs not working for me. Used the last sheet I uploaded and copy/pasted your last code. Got error 1004. "Unable to set the visible property of the Pivotitem class" which was the error I was getting before.

    Tried on Excel 2007 and 2016. Same issue.
    Could you attach a sheet where the maco works on your machine?
    Running out of ideas

  11. #11
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Macro to select multiple pivot table filters, based on values in a table

    I'm not out of ideas - I had tried something that failed; but it actually allowed what I posted to work, though I did not realize it at the time:

    This WILL work (I hope )

    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Macro to select multiple pivot table filters, based on values in a table

    Horay!! thank you!

+ 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. Replies: 3
    Last Post: 09-27-2018, 11:12 AM
  2. macro that filters pivot table and copy values to other sheet
    By arvimeld in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2018, 02:47 AM
  3. Macro to update pivot table filters from multiple pivot tables of different data sources
    By groblerdn85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2017, 05:10 AM
  4. Applying multiple values filters on a single variable (pivot table)
    By greengirl in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-29-2015, 08:24 PM
  5. VBA select multiple values in a pivot table field based on a list
    By DD1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2014, 08:28 PM
  6. Replies: 8
    Last Post: 06-24-2014, 03:35 PM
  7. Changing the Pivot Table Filters based on Multiple Cells
    By dbnhc8 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-28-2012, 02:37 PM

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