Master Filter Sheet to Filter All Sheets

Hello,

I have a workbook with 17 sheets. Each one on the 1st column is a Employee ID with a filter. Some sheets may or may not have the employee ID depending on the information but most do.

Is there a way I can make it easier for the end user to filter out user ID's without having to do it 17 times?

I'm decently comfortable with VBA.

Thanks!

Sorry about being slow on getting back; busy and this was a bit more involved than I originally thought.

How Attached File Macro Works:
1) The attached code copies the autofiltering criteria from ANY source sheet to an array of target sheets that you setup in the macro. Note that the macro is NOT based on copying the filtering from ONLY the 1st sheet to the other sheets (so this is a slight change from what you orginially asked for). In other words, if you are sitting in the 2nd sheet and run the macro, then it copies the filtering in the 2nd sheet to all the other target (array) sheets.
2) If NO filtering exists at all (the 'no arrows' case), it prompts user to abort or continue. If continue, then it removes all filtering from the other target sheets.
3) If filtering exists but all columns are set to "Show All", then it also prompts user to abort or continue. If continue, then it invokes the same "Show All" type of filtering to the other target sheets.
4) The last option is the main one that you are after: If filtering exists and one or more of the columns do have active filtering criteria, then it invokes this exact same criteria pattern (for all columns) to the target sheets.

Here is a list of things in the macro that you will have to tweak to fit your needs:
1) Immediately below the DIM lines, change the "Data1" names to match your real sheet names for the sheets that you want to have equal filtering. Include ALL sheets that you want to have the SAME filtering, including the 1st sheet. Exclude any sheets that you don't want to include in copying the filtering criteria to. So, if you have 30 sheets that should have common filtering, then, when you are done here, you should have 30 sheet names loaded into this array variable. And, if there are 2 other sheets (for example) that you don't want to have this same filtering, then exclude them from loading into this array. ... ANOTHER (maybe better) WAY: If the name or number of these 'common' sheets changes a lot and manually loading these names is a PAIN, then there is a much better and easier way to do this compared to this "manual load array" technique. ... Instead of a "For i = 1 To UBound(DataShts)" loop, you'd use a "For Each Sh in Worksheets" collection loop. ... Even if you have just a few sheets that you want to exclude, you can still use the "For Each" technique, but then advance to the next sheet when one of those 'excluded' sheets comes up. ... If you'd rather do it this way and need help, holler back!
2) Continuing on, now go to top of macro and change the array size from (3) to (30) or whatever number of sheets you have written out to load into this array.
3) I put some code in the 'ThisWorkbook' module so to force the shortcut keys (Cntrl + Shift + X) to invoke this macro. If you prefer a different key combination, then make this change and re-run this 'Document_Open' macro. Remember that if you enter a LARGE-case letter (like 'F') that this translates to Cntrl + Shift + F, but if you enter a small-case letter (like 'f') that this translates to Cntrl + f (no shift involved here).

If you like the solution here, please change post status to SOLVED (see rules for instructions) and, if you really like the solution, then please hit the scales (). Otherwise, holler back if you have further questions or have other issues. ... sauerj.

Why spread the information across 17 sheets? AutoFilter is designed to Filter data on one sheet, so have all the information on one sheet & a column for Employee Id then filter by that and whatever the other criteria is.

That's because it's not designed properly, Why do you need a sheet for each employee?

I can visualize what royUK is driving at. What about the following structure?:
1) Insert a new 'A' column with heading: "Product Set".
2) For the Sheet1 data, type "Set 1" in these cells below heading (or just "1", or "1" w/ custom formatting to put prefix "Set " before #).
3) Move sheet2 data to bottom of sheet1 data and type "Set 2" ... following same pattern as #2 above.
4) Move sheet3 & the rest of the sheetX data appending to the bottom of sheet1 and typing in the appropriate "Set X" text for each of these sets of data.
In the end, you will have ALL the data in one sheet and everyone can filter either the "Product Set" column or the "ID" column, thereby filtering everything in one shot.
I realize that you only know your details best (been in position where suggestions don't fit the constraints that come from other production anomalies), but curious if the above structure might work (???).

Reading over your code opened the door to teach me about ListObjects. Good to know!

Please Login or Register  to view this content.

Hello nguyeda,

if you can move the 17 sheets data to a single sheet, you can also use formula in a separate sheet to filter data. Also you can choose the options to filter, Employee, Product, Location.... depends on your data.

I don't think you can feed string or variant Arrays() into the criteria arguments in the way you are doing. When I tested your code, this approached seemed to be a major show stopper. And, the "Operator:" variable needs to be clearly defined as either xlOr or xlAnd. I assume that you may want both options. ... So, I punted and opted to ask user 3 separate INPUTS (#1 is Criteria1, #2 is Criteria2 and #3 is the Operator). You only get TWO criterias using Autofilters!
1) If you CANCEL at 1st question, it exits sub.
2) If you CANCEL at 2nd question, then it only uses Criteria1 (Criteria2 is not used).
3) It only asks the 3rd (Operator) question, if you enter non-NULL on Criteria2 question.
4) If you CANCEL at Operator question, it exits sub.
5) If you type any sort of upper or lower case "clear" at 1st question, it removes all filtering.
6) It does the exact same filtering to all sheets (looping thru all of them).
7) It only filters based on values in column 'A' in all the sheets.
Try this out! Let us know if this is close to what you need (). ... sauerj

Nevermind, I got it:

Please Login or Register  to view this content.

That's wrong. Nvm