Master Filter Sheet to Filter All Sheets

**nguyeda** · 06-06-2011, 02:32 PM

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!

**sauerj** · 06-07-2011, 07:12 PM

For all 17 sheets, will the exact same filter value be used? If so, maybe the following outline ...
1) Use in InputBox to get desired filter from user,
2) Execute a 'For Each Sheet' loop and execute Auto-Filter type code using the retrieve InputBox value as the filter criteria in the 'A' column.
Does this outline sound close to what you need?

**nguyeda** · 06-08-2011, 09:56 AM

Originally Posted by sauerj

For all 17 sheets, will the exact same filter value be used? If so, maybe the following outline ...
1) Use in InputBox to get desired filter from user,
2) Execute a 'For Each Sheet' loop and execute Auto-Filter type code using the retrieve InputBox value as the filter criteria in the 'A' column.
Does this outline sound close to what you need?

Yes exactly. How do you do that?

**sauerj** · 06-08-2011, 11:37 AM

Please post an example workbook as an attached file having the 17 sheets with a few rows of data, and give a 3-4 examples of what you would filter for in coumn 'A'. ... thanks!

**nguyeda** · 06-08-2011, 01:57 PM

It's really just a 17 sheets with 5 to 10 columns with alphanemuaric data.

Column A is always 3 letters and 3 numbers like:

ASD123
DSK234
ASF324

There are about 15 unique identifiers.

**sauerj** · 06-12-2011, 11:31 AM

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.

**mtm70** · 12-11-2012, 05:01 AM

Hi There,

Sorry for piggy-backing on an old thread, however, this code is excellent and I need some further information
this works great for a selection of 2 options in the column but does not work if I wanted to select 3 or more options - how would I tweak the code to include these extra options

**sauerj** · 12-11-2012, 11:34 PM

mtm70, Sorry, I can't help you much. Too busy. The old code was intended for Excel2003. You want it to work for Excel2007/10. The problem is that Excel2003 only had 6 "operators" and Excel2007/10 has 11 "operators" and can accommodate arrays for the criterias. Try recording a macro where you select 3 check box options from the filter drop down, and you will see how criteria1 is an array and the operator = xlFilterValues (7). The code needs to be adjusted for these new options. Hope you can take it from here or solicit someone else to help. Sorry, just can't help at this time.

**mtm70** · 12-12-2012, 08:37 PM

No problem!

That's a brilliant idea thought. I will let you know if it works. I really appreciate you getting back to me

**sauerj** · 12-15-2012, 07:17 PM

mtm70, Modified & attached altered worksheet that (I think) will work for you. The lines numbered 101-103 & 110 are the altered lines. The fix involved dealing with the expanded version of 'operator' and using [IsEmpty()] instead of [= ""] to work with arrays for the "Criteria1" variable. Holler back if this doesn't work for you. sauerj
FilterSheets(2012-12-15).xls

**nguyeda** · 06-13-2011, 12:30 PM

Omg I wish I saw this earlier. I came up with this on Friday and worked on it this morning but I know there is an easier way with better looping but I'm just a super beginer at VBA.

User would either type their user code or type clear to clear all the filters. Or cancel to get out of it all.

Sub Rectangle1_Click()
    Dim UserInput As String
    Dim strWorksheet As String
    Dim strWorkSheetPath As String
    Dim sht As Worksheet
    Dim wkb As Workbook
    Dim rng As Range
    
    
    
UserInput = InputBox("Enter User Code ", "Auto Filter")
If UserInput = vbNullString Then Exit Sub
  
    
Set wkb = ActiveWorkbook
Set sht = Worksheets(1)
Set sht1 = Worksheets(2)
Set sht2 = Worksheets(3)
Set sht3 = Worksheets(4)
Set sht4 = Worksheets(5)
Set sht5 = Worksheets(6)
Set sht6 = Worksheets(7)
Set sht7 = Worksheets(8)
Set sht8 = Worksheets(9)
Set sht9 = Worksheets(10)
Set sht10 = Worksheets(11)
Set sht11 = Worksheets(12)
Set sht12 = Worksheets(13)
Set sht13 = Worksheets(14)
Set sht14 = Worksheets(15)
Set sht15 = Worksheets(16)
Set sht16 = Worksheets(17)
                               
           
sht.Activate

With sht

    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
  
    
End With

sht1.Activate

With sht1
   If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht2.Activate

With sht2
   If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht3.Activate

With sht3
    
    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht4.Activate

With sht4
    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null

End With

sht5.Activate

With sht5
    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht6.Activate

With sht6
     If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht7.Activate

With sht7
    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht8.Activate

With sht8
      If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht9.Activate

With sht9
     If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht10.Activate

With sht10
     If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With


sht11.Activate

With sht11
  If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht12.Activate

With sht12
  If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht13.Activate

With sht13
    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht14.Activate

With sht14
      If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht15.Activate

With sht15
    If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
End With

sht16.Activate

With sht16
     If UserInput <> "clear" Then ActiveSheet.ListObjects("Table17").Range.AutoFilter Field:=1, Criteria1:=UserInput
    If UserInput = "clear" Then ActiveSheet.ListObjects("Table17").Range.AutoFilter Field:=1, Criteria1:=Null
End With
    
sht.Activate
    
End Sub

**royUK** · 06-13-2011, 12:47 PM

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.

**nguyeda** · 06-13-2011, 01:26 PM

Mine is really specific to my needs. I have 17 sheets and only need to filter by column A. That's all. It does it but it looks very inefficient.

**royUK** · 06-13-2011, 01:44 PM

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

**nguyeda** · 06-13-2011, 02:03 PM

There's isn't a sheet per employee. Theres 17 different sheets of data. There are 20 employees and they filter on each sheet to see their respecitive product information.

**sauerj** · 06-13-2011, 05:20 PM

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!

**snb** · 06-13-2011, 05:38 PM

Sub Rectangle1_Click()
  c01 = InputBox("Enter User Code ", "Auto Filter")

  If c01 <> vbNullString Then
    for each sh in thisworkbook.sheets
      Sh.ListObjects("Table6").Range.AutoFilter 1, c01
    next  
  end if
End With

**Haseeb Avarakkan** · 06-13-2011, 05:51 PM

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.

**nguyeda** · 06-14-2011, 12:06 PM

No, the data cannot be moved to 1 sheet. That'd be great though and I see how that'd be easier. Each sheet covers actions like buy products, fixing them, information from our vendors then formulas based on the different quantities that are being shipped to us or broken. So each sheet is a completely different report.

Thanks for the advice.

snb, that code looks good, I'm going to test it soon! Thanks!

**nguyeda** · 06-14-2011, 01:19 PM

snb, I can't get that code to work. It says error 9, subject out of range and highlights

 sh.ListObjects("Table6").Range.AutoFilter 1, Criteria1:=c01

**nguyeda** · 06-14-2011, 01:24 PM

Is it also possible for the input box to collect more than 1 string? If i wanted to filter 2 or more user codes?

**nguyeda** · 06-14-2011, 04:38 PM

Sub Rectangle1_Click()
    Dim UserInput As Variant    'also tried string
    Dim strWorksheet As String
    Dim strWorkSheetPath As String
    Dim sht As Worksheet
    Dim wkb As Workbook
    Dim rng As Range
    Dim MySplit As String
    
    
    
UserInput = InputBox("Enter User Code ", "Auto Filter")
myarray = Array(UserInput)
If UserInput = vbNullString Then Exit Sub
   
Set wkb = ActiveWorkbook
Set sht = Worksheets(1)


           
sht.Activate

With sht

   If UserInput <> "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=(myarray), Operator:=xlFilterValues
   If UserInput = "clear" Then ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=Null
  
    
End With


End Sub

Why won't this work? I've been at this for hours trying different things.

The macro in excel records taking multiple filters as:

ActiveSheet.ListObjects("Table6").Range.AutoFilter Field:=1, Criteria1:=("abc123", "thw93", "def143"), Operator:=xlFilterValues

**sauerj** · 06-14-2011, 06:28 PM

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

**nguyeda** · 06-14-2011, 07:41 PM

That's a great idea! but my team that I'm supplying this to is often intimidated by excel. I think the constant questions are going to get them to not use this feature. Thanks for letting me know it's not possible to do it all on 1 question so I can stop using inputbox.

Is it possible to create a form that captures the data to make it work?

**sauerj** · 06-14-2011, 11:41 PM

Yes, the UserForm idea is 10x better. Good idea! And, not too hard to do; kinda cool now! Code pretty streamlined now. I bet you'll find this pretty close to meeting your needs (

). ... sauerj

**nguyeda** · 06-15-2011, 08:49 AM

This is excellent! You're great man!

How can I get the "Clear All Filters" button to clear out the criteria but not the filters completly?

**sauerj** · 06-15-2011, 02:28 PM

Originally Posted by nguyeda

How can I get the "Clear All Filters" button to clear out the criteria but not the filters completly?

Did you get this OK? If not, here would be change to make: In the "Sub AutoFilter_AllSheets" macro change

If Len(Crit1) = 0 Then .ListObjects(1).Range.AutoFilter

to the following ...

If Len(Crit1) = 0 Then .ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=Null

**nguyeda** · 06-15-2011, 04:27 PM

Yup, that's exactly what I figured out just a moment ago. Thanks for your help!

**nguyeda** · 06-15-2011, 10:00 AM

Nevermind, I got it:

Private Sub CANCEL_Btn_Click()
 .ListObjects(1).Range.AutoFilter Field:=1, Criteria1:=Null
End Sub

That's wrong. Nvm

Master Filter Sheet to Filter All Sheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Re: Master Filter Sheet to Filter All Sheets

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions