+ Reply to Thread
Results 1 to 11 of 11

Reapply Filters for MULTIPLE tables Automatically

  1. #1
    Registered User
    Join Date
    11-05-2013
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2007
    Posts
    5

    Reapply Filters for MULTIPLE tables Automatically

    Hi, my name is Sean. I have been working with excel for quite a while and have begun getting into VBA to make my life and others easier. However i am stuck on this issue.

    I have a workbook that contains 1)A spreadsheet of data (names, departments, etc), we will call this "DATA SHEET", and 2) A spreadsheet with 20 tables, we can call "Table Sheet". Each table represents a department, and filters out all employees that are not with that dept. Simply put, i am looking for a way to automatically reapply the filters on these tables when i change a point of data, without having to click reapply 20 times.

    A little background which may shed some light and possibly provide alternate solutions.

    In the search for a way to create a table for each dept. that would auto update as i change/add/remove from the data, i came across a couple solutions. 1) use an array formula to determine a unique list of people under that dept. While this was a simple and effective solution, when copied over the required ~2000 rows it became painstakingly slow, so that was thrown out of the window. or 2) use 20 tables, all linked to the data directly, apply filters, and use VBA to auto reapply. I had never used VBA for this and was unsure where to start, so i ran to the internet and found a couple helpful, but incomplete answers. I found a way to auto reapply a filter that was on a sheet (but NOT a table), and then found one that claimed to work for tables, however i could not reproduce that said success.

    Thank you in advance for any help you can provide.

  2. #2
    Registered User
    Join Date
    11-05-2013
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reapply Filters for MULTIPLE tables Automatically


  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Reapply Filters for MULTIPLE tables Automatically

    why do you need the separate tables-what are they for?
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    11-05-2013
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reapply Filters for MULTIPLE tables Automatically

    Excel Forum Plz Help.xlsm

    JosephP,

    The reason i went with multiple tables is so i could have multiple filters applied to the same column on 1 sheet. Without creating the tables i would not be able to achieve this due to filter restrictions in excel. Creating the tables allows me to have a different set of filter criteria for each, and keep them all nice and neat on the same sheet. I have created an example workbook illustrating what i have done that may shed some light.

    Each table is a direct reference to the data. Then filters out all non active employees and all who are not in that tables department. This way i get a departmentalized list of employees, that in theory, when all is done, would automatically change whenever i change the data. Right now when i change the data i have to click the reapply filters button for each table. I am trying to bypass this with a script that will reapply the filters for each table whenever i make a change to the data.

    Like i stated in the OP, i have tried using an array formula, which works great when you only have a handful of departments/employees, however changes to a monster time consumer when dealing with the reality of 250+ employees with 20 departments.
    Last edited by CrazyCookie; 11-06-2013 at 11:27 AM.

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Reapply Filters for MULTIPLE tables Automatically

    what I'm really asking is why you need multiple representations of the data at the same time rather than simply filtering one table for whichever dataset you require at any given moment?

  6. #6
    Registered User
    Join Date
    11-05-2013
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reapply Filters for MULTIPLE tables Automatically

    Ah ok.

    The goal is to create a departmental list, that can be PDF'ed/printed that automatically updates when the data is changed. Therefore i need so see ALL departments on one sheet, not one at a time. If there is another solution to this that does not require tables AND will have a low calculation time i am all ears. Again the reason i went with this method is that IF there exists a simple script that will auto reapply the filters everytime i change the data, the calculation time would be minimal compared to other options using formulas. When i created this using array formulas, i was presented with a calculation time > 1 minute.

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Reapply Filters for MULTIPLE tables Automatically

    can't you just sort the table by department and filter it? (you might still need a couple tables depending on the filters you need)

    anyway the code is pretty simple
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    11-05-2013
    Location
    Sanford, FL
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reapply Filters for MULTIPLE tables Automatically

    Thanks that works perfect!

  9. #9
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Reapply Filters for MULTIPLE tables Automatically

    you're welcome :-)

  10. #10
    Registered User
    Join Date
    07-13-2012
    Location
    Calgary, Alberta, Canada
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Reapply Filters for MULTIPLE tables Automatically

    Hi! Is there a way to apply this to the same issue but across multiple sheets? One sub that will update all of the tables on all of the tabs?

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Reapply Filters for MULTIPLE tables Automatically

    Oksana,
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

+ 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. Macro to apply filters to multiple tables
    By mo0ki in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2012, 10:57 PM
  2. Macro for linking filters with-in multiple pivot tables
    By Nick Simo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-12-2012, 03:36 PM
  3. Pivot tables - Multiple filters across 2 tables
    By shoesterix in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2011, 05:02 AM
  4. Applying filters to multiple pivot tables across multiple worksheets
    By gbarragan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-11-2010, 12:05 PM
  5. Automatically reapply filter based on date
    By Floris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-30-2010, 04:12 AM

Tags for this Thread

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