Autofilter - modify code a bit 1

[cutestuff]

hi,
I have a workbook containing several sheets wherein on 4 of the sheets there is one identical column that we need to filter by.
I have the following code below. I've tested this code and it works - but only if ALL the sheets have the same identical columns. Like I said above, we have 3 sheets that contain completely different information. How can I modify the code below to make this work? Also, I have hardcoded the criteria. I would like it to be so that when a user uses the autofilter on column1, the criteria gets set to it.

Sub Test()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Range("A2").CurrentRegion.AutoFilter Field:=1, Criteria1:="03-00018-20-107-B-02-00-303-C"
Next

End Sub

Thank you so very much in advance.
Any help or push in the right direction is appreciated.

 

[tbl]

Surely you just need to write a bit of code at the beginning of your routine to detect the column which you want to filter. If you can't detect this by logical means the you also can't program it. If your situation is that you are scanning all of the sheets in a book and you wish to set a filter on all columns containing particular data, you first need to see if the data occurs, and then see in which column, and then pass this info to your routine.
If you can confirm that this is in fact what you are looking for I can help you with the code.

Richard

 

[cutestuff]

hi,

Yes. I have one column (the first column) where the unique id exists (where the user will filter). On 4 of the 7 sheets this column exists. The other 3 sheets are just reference sheets. So if I'm a user and I filter column1 for a particular id (on ANY of the 4 sheets that have the column), the corresponding sheets should autofilter to that column too.
thank you in advance...

 

[tbl]

If we take your original code, which you say works, and modify it to decide whether the column should be filtered or not we will have something along these lines

Sub Test()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

If something unique is found in column 1 then
ws.Range("A2").CurrentRegion.AutoFilter Field:=1, Criteria1:="03-00018-20-107-B-02-00-303-C"
End If
Next

End Sub

I don't understand what you want to do with your criteria, but you can always make a variable out out of it and let the user decide what their filter criteria should be.

 

[cutestuff]

hi tbl,

Thank you!
I actually got it to work.
I used the following:

Set wbData = ActiveWorkbook
For Each ws In wbData.Sheets
If ws.Name = "Index" Then goto nextline
ws.Range("A2").CurrentRegion.AutoFilter rngFilt, rngCrit

nextline:
Next

Worked like a charm! Thank you for your help though! Appreciate it!