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.
Bookmarks