+ Reply to Thread
Results 1 to 2 of 2

Macro to update a pivot table containing multiple filters (not multiple items in a filter)

  1. #1
    Forum Contributor
    Join Date
    03-23-2012
    Location
    Michigan
    MS-Off Ver
    MS365 Excel version 2301 (Build 16026.20238)
    Posts
    101

    Macro to update a pivot table containing multiple filters (not multiple items in a filter)

    Hello. I've got three pivot tables attached to a data table on a worksheet. Each pivot table has five filters, with four of them being the same. Rather than have the user update the three pivot tables separately, I've got a small table where they enter the four parameters once. Then via a macro, I want the four filters in each of the pivot tables to get updated with the parameters the user entered. I've got it working nicely for the first filter (Country of Event, shown below), but it doesn't update subsequent filters. The Catalog # code below seems to work (no debug), but it doesn't update the pivot table. Everything I've found online is for updating multiple items within a filter. Is there something else I need to do to get the other filters to update, or is it a limitation to only be able to update one filter? Thanks for any advice.

    'update Country of Event in pivot tables
    Set xPTcoi = Worksheets("SIMPLE").PivotTables("PivotCOI")
    Set xPFcoi = xPTcoi.PivotFields("Country of Event")

    xStrCOI = Range("B4").Value
    xPFcoi.CurrentPage = xStrCOI

    'update Catalog # in pivot tables
    Set xPTcoi = Worksheets("SIMPLE").PivotTables("PivotCOI")
    Set xPFcoiCatalog = xPTcoi.PivotFields("Catalog #")

    xStrCatalog = Range("B5").Value
    xPFcoiCatalog.CurrentPage = xStrCatalog

  2. #2
    Forum Contributor
    Join Date
    03-23-2012
    Location
    Michigan
    MS-Off Ver
    MS365 Excel version 2301 (Build 16026.20238)
    Posts
    101

    Re: Macro to update a pivot table containing multiple filters (not multiple items in a fil

    Hi again. Just a follow up. I tried recording a macro where I selected the multiple items, and below shows the code for the first two filters. But when I reset the pivot table and try running this code, it still bugs out on the second pivot field. Thanks.

    ActiveSheet.PivotTables("PivotCOI").PivotFields("Country of Event").ClearAllFilters
    ActiveSheet.PivotTables("PivotCOI").PivotFields("Country of Event").CurrentPage = "Xanadu"
    ActiveSheet.PivotTables("PivotCOI").PivotFields("Catalog #").ClearAllFilters
    ActiveSheet.PivotTables("PivotCOI").PivotFields("Catalog #").CurrentPage = "1234455234"

+ 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. [SOLVED] Macro to select multiple pivot table filters, based on values in a table
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2018, 06:42 PM
  2. [SOLVED] Using VBA to update pivot table filter from multiple values in a list
    By tyler0320 in forum Excel General
    Replies: 7
    Last Post: 04-09-2018, 03:49 PM
  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. Count Multiple Items in a Pivot Table filter
    By Karen13 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-16-2015, 05:49 AM
  5. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  6. 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
  7. Controlling multiple pivot table filters with one filter.
    By adoepker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2011, 03:41 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