+ Reply to Thread
Results 1 to 2 of 2

Setting Macro's for filtering a pivot table with data updates

  1. #1
    Registered User
    Join Date
    06-06-2017
    Location
    Kortrijk
    MS-Off Ver
    Office 365
    Posts
    1

    Setting Macro's for filtering a pivot table with data updates

    Dear Forum,

    I'm currently working on a global sales analysis of the company i work for using pivot tables and macro's.
    The goal is to be able to load in new sales data into the pivot table, and get filtered data just by pressing buttons( macro's).
    This is not so hard, but the problem occurs when you load in new sales data with either new Article numbers or new clients.

    Because when recording the macro, it is recording all the items that you DON'T want to select, instead of the items you do want to select.
    Because of this, when loading in new sales data with new article numbers, the new article numbers will automaticly be visible as they were not set to False in the original Macro.

    for example:

    With ActiveSheet.PivotTables("Draaitabel5").PivotFields("agi_art")
    .PivotItems("100005").Visible = False
    .PivotItems("100005NL").Visible = False
    .PivotItems("100057").Visible = False
    .PivotItems("100057NL").Visible = False
    .PivotItems("100062NL").Visible = False
    .PivotItems("100064").Visible = False
    ...

    I would like to be able to write my filtering macro so it would say something like this:

    With ActiveSheet.PivotTables("Draaitabel5").PivotFields("agi_art")
    .PivotItems("100005").Visible = True
    .PivotItems("All other article numbers").Visible = False

    Any help would be appreciated.

    Thank you in advance.

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

    Re: Setting Macro's for filtering a pivot table with data updates

    You may not need VBA at all. You might be able to do this with helper columns in the source data. It is almost impossible to make sense of a pivot table question without talking about the source data upon which it is built.

    Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    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.

+ 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. Excel Macro Pivot Table filtering
    By Jovillanueva in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-16-2013, 05:27 AM
  2. [SOLVED] Macro that automatically updates pivot table when data in separate worksheet is updated
    By Femi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-09-2013, 08:48 AM
  3. [SOLVED] Filtering Data Pivot Table is Based On - Impact on Pivot Table
    By bbg22 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-17-2013, 06:34 AM
  4. Macro Error when Filtering in a Pivot Table
    By ssteines in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2013, 03:15 PM
  5. Macro for filtering table and pivot
    By Polglish in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-02-2013, 03:05 AM
  6. Macro for selecting a single item while filtering the data in Pivot Table
    By Syed964 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 01:28 PM
  7. Filtering Issues with Macro for Creating Pivot Table
    By Mathaholic14641 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-22-2012, 09:59 AM
  8. Macro for Filtering in a Pivot Table
    By Herve_Rob in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-16-2007, 12:29 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