+ Reply to Thread
Results 1 to 1 of 1

Must Advanced Filter be used with Pivot Table Slicers in order to auto copy and paste data

  1. #1
    Registered User
    Join Date
    Seattle, WA
    MS-Off Ver

    Must Advanced Filter be used with Pivot Table Slicers in order to auto copy and paste data

    UPDATE: Apparently all I had to do was manually go back in to the table and add the filter.
    I found, what I think is, the solution to weeks of research, testing and work BUT there is one thing that I am hoping to change.

    I have a large excel spreadsheet (2500+ rows x 200+ columns) where I keep detailed information on every product we carry (no sales information - strictly product information: UPC codes, package sizes available, dimensions, pricing, etc.). I have been looking for a way to more easily share the information with users that want a very abbreviated version of the spreadsheet for quick reference. After trying multiple methods that served the purpose well but, based on the quantity of formulas, it would bring work to a complete halt, I found a method using a pivot table, slicers, a macro button and the advanced filter, that did the job beautifully - not only did it present the abbreviated information, but it allows the data to be manipulated without changing anything on the master spreadsheet. However, I don't like that the advanced filter overrides the auto-filter on the excel table where the information is all being generated from. I am constantly adding and updating information in the table and use the auto-filter extensively. I am wondering if there is a way to accomplish the same end result as with the pivot table/slicers/macro/advanced filter but without the advanced filter?

    I would post the link to the site where I found the tutorial but, I am unclear if that is against the forum rules for this site...

    Essentially what I ended up with was 3 tabs: one had the item information table, another had the pivot table report filters and the last one presented the abbreviated information. It had two slicers where you would pick the supplier name and the brand of the product, once you picked those you clicked the macro button and the chosen, associated information was populated below. The information that you saw was copy and pasted as values, according to the column heading (they were labeled the same as the headings from the table).

    Overall, this has been the best method I have found, the calculations have not interfered with the usability of the workbook - there is no noticeable delay - it's just the darned advanced filter that is throwing a wrench in the "perfect solution". Any help would be greatly appreciated.

    Please accept my apologies if I have done or said anything contrary to forum rules, I am very new to all of this.

    Last edited by LuScannon; 03-07-2017 at 02:08 PM. Reason: Problem Solved

+ 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. how to make pivot table auto filter which is source data
    By Anisusa in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-15-2017, 03:44 AM
  2. [SOLVED] sorting date data in pivot table that has slicers connected to it
    By retzlar in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-26-2015, 05:11 AM
  3. [SOLVED] Disconnect Slicers, Change Pivot Table Source, Reconnect Slicers: a problem
    By Geoff. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2015, 02:27 PM
  4. Is it possible for slicers in pivot table to also filter data sheet?
    By shelly2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-22-2014, 07:08 PM
  5. How-to Copy and Paste Values from list into Pivot Table filter
    By MacroNerd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-05-2013, 06:26 PM
  6. Use combobox value to filter Pivot Table like slicers using VBA
    By limalf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2013, 07:31 PM
  7. Replies: 2
    Last Post: 05-24-2013, 03:58 PM

Tags for this Thread


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