Autofilter on another sheet

[n0gel]

Hello forum, I'm trying to put together a macro that will run an autofilter on a range on another sheet, and return the filtered results to the sheet from which I ran the macro. So the same macro will work on all sheets without modifying the code for each sheet to reflect that sheet's name.
So I start out on sheet3 say,
then run the macro from sheet3,
it does an autofilter on sheet1 say, and copies the result back to what ever sheet I started from, in this case sheet3.
And the same macro would work if I started on sheet 20.

Thanks forum, I always appreciate your help.

edit: And the filter criteria is located in a cell on the sheets from which the macro is run and to which the data will be copied. So each sheet will have 1 cell each containing the filter criteria to use on the other sheet, or sheet 1 in this case.

 

[jbeaucaire]

Something like this...

Sub FilterSheet1()
'Filters sheet1 by a specific column, copies results to current sheet at A4
Dim fCol As Long    'column to filter on sheet1
Dim Crit As String  'value to filter by
Dim LR   As Long    'last row of visible filtered data

fCol = 1                            'Column A is 1, column B is 2, etc
Crit = Range("A1").Value            'uses value in A1 for the filter
Application.ScreenUpdating = False  'speed up macro

With Sheets("Sheet1")
    .AutoFilterMode = False
    .Rows(1).AutoFilter Field:=fCol, Criteria1:=Crit
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    If LR > 1 Then
        .Range("A1:A" & LR).EntireRow.Copy Range("A4")
    Else
        Range("A4") = "No results found"
    End If
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True
End Sub

The part in red is the target on the activesheet, adjust that to where you want the list to display.

 

[n0gel]

Wow. As soon as I tested your code about six more uses for this came to mind.
Thank you very much!