Filter according to 'typed' value

[Closed]
Report
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
Hello,

I need an autofilter to change according to a 'typed' value on another worksheet.

I have found this code, which works when the typed value is on the same sheet as the filter, but it doesn't work when the typed value is on a different sheet:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub Worksheet_Change(ByVal Target As Range)
If (Intersect(Target, Range("E1")) Is Nothing) _
Then
Exit Sub
End If
Cells.AutoFilter Field:=1, Criteria1:="=" & Range("E1")
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Any help gratefully received. Thank you.

Matthew

1 reply

Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
Hi Matthew,

You will need to refer to the sheet where the autofilter needs to be applied like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
Sheets("MySheet").Cells.AutoFilter Field:=1, Criteria1:="=" & Target
End Sub


Best regards,
Trowa