+ Reply to Thread
Results 1 to 4 of 4

Inserting filter into pivot table via macro errors if filter doesn't exist.

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,179

    Inserting filter into pivot table via macro errors if filter doesn't exist.

    Below is my current workaround for activating filters for a pivot table. It works great as long as that value exists in the pivot table, otherwise it errors out. I need to either activate the last five business days, and if one doesn't exist skip, or activate the last five business days that do exist. Either way.


    Please Login or Register  to view this content.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,146

    Re: Inserting filter into pivot table via macro errors if filter doesn't exist.

    It would help a lot with a sample file with representative data. In most cases, it's difficult to answer pivot table questions without seeing how the source data are organized.

    Also the way I check to see if an item is available for filtering is to do a COUNTIF on the column. If it comes up zero, I can't filter on it.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,179

    Re: Inserting filter into pivot table via macro errors if filter doesn't exist.

    Attached is a general idea. The macro puts the formula into P1:T1 and the pivots look to that to see if it can enable a filter. Issue is, if there is no item with that date the pivot will error out. If it would skip the date then that would be ok too.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,179

    Re: Inserting filter into pivot table via macro errors if filter doesn't exist.

    Hey dflak, any idea?

+ 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. Assigning pivot filter that doesn't exist breaks macro
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2017, 06:22 PM
  2. Macro to Filter Pivot Table not working when the Value does not exist
    By mtakawane in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-01-2017, 07:50 AM
  3. [SOLVED] Select Automatic Report Filter in Pivot where Filter exist in Data table else select Blank
    By vinaynaran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-02-2016, 08:47 AM
  4. [SOLVED] Pivot filter sees duplicates when table doesn't
    By nigelbloomy in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-03-2015, 01:39 PM
  5. Replies: 1
    Last Post: 02-07-2014, 03:12 AM
  6. [SOLVED] How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?
    By Rhino_dance in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-28-2013, 11:01 AM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM

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