Previous Topic Next Topic

Filter according to 'typed' value

Closed
QPRMinty - Feb 26, 2015 at 06:32 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - Mar 2, 2015 at 11:31 AM
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

Related:

1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
Mar 2, 2015 at 11:31 AM
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