using same filter in multiple sheets

[krips]

This is my scenario.


- I have close to 20 worksheets in my Excel file. They have the same header columns.
- I need to filter column C, the criteria being a text ("ABC")
- This filter needs to go in all the sheets.


Can anyone suggest a VB code for this? I am not familiar with VB and codes.

Thanks,
Kripesh

 

[mumps]

Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub filterSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        ws.Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:="ABC"
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[krips]

Sorry for being late on this. Thanks a lot, @mumps.

 

[mumps]

You are very welcome.

 

[Dmitry_D_Onishchenko]

Dear @mumps , could you please help me with updating this code to filter table data, not just plain ranges? Specifically, I'm struggling with updating the following part:

ws.Range("I1:I" & LastRow).AutoFilter Field:=1, Criteria1:="FO 1% FOB MED"

Ideally, I'd like to have a code applicable to both tables and ranges.

Thanks,
Dmitry

 

[My Aswer Is This]

Here is how I do things:

Assuming your Table name is "Cars"

Sub Filter_Me()
'Modified  11/27/2019  9:59:27 AM  EST
Dim ans As String
ans = InputBox("Enter filter value")
    ActiveSheet.ListObjects("Cars").Range.AutoFilter Field:=1, Criteria1:=ans
End Sub

 

[Dmitry_D_Onishchenko]

Thanks a lot @My Aswer Is This , it works fine for me.

In addition, if I do not know the name of the table ("Cars" in the example you've provided) - say, doing a loop through multiple sheets with multiple tables on them - I can extract it directly from the ActiveSheet.ListObjects as shown here.

Have a nice day!

 

[My Aswer Is This]

Well you do not need the Table names.

Try this will filter all Tables in your workbook.

Sub Filter_Me()
'Modified  11/27/2019  11:07:00 AM  EST
Dim ans As String
Dim T As ListObject
ans = InputBox("Enter filter value")
    
For i = 1 To Sheets.Count

    For Each T In Sheets(i).ListObjects
        T.Range.AutoFilter Field:=1, Criteria1:=ans
    Next
Next
End Sub

 

[Dmitry_D_Onishchenko]

@My Aswer Is This , fair enough. You're right there is no need in explicit table names; however, I need to know whether a table exists on the active worksheet or is it a plain range. I do this as follows:

If ws.ListObjects.Count > 0 Then
    ' do stuff suitable for tables, looping as you suggested above
Else
    ' do stuff suitable for ranges
End If

Thanks!

 

[My Aswer Is This]

@My Aswer Is This , fair enough. You're right there is no need in explicit table names; however, I need to know whether a table exists on the active worksheet or is it a plain range. I do this as follows:

If ws.ListObjects.Count > 0 Then
    ' do stuff suitable for tables, looping as you suggested above
Else
    ' do stuff suitable for ranges
End If

Thanks!

OK. Is their a question you have