VBA: Getting error method range of object _worksheet failed

I keep getting error as mentioned in the title when executing the below code:

Sub test()
    Dim j As Integer
    j = 2
    If ActiveSheet.Name = "Email" Then
        Sheets("Lists").Range("Z2:Z31").Cells.Value = ""
        For i = 2 To 190
            If Sheets("Lists").Cells(i, 8).Value = Cells(2, 2).Value Then
                Sheets("Lists").Cells(j, 26).Value = Sheets("Lists").Cells(i, 6).Value
                j = j + 1
            End If
        Next
        Range("C12:O12").AutoFilter 1, ">=" & Range("B1").Value, xlAnd, "<=" & Range("C1").Value
    End If
End Sub

This code is automatically executed when any change happens in the worksheet by adding this class event.

Public WithEvents appevent As Application
Private Sub appevent_SheetChange(ByVal sh As Object, ByVal target As Range)
    Call test
End Sub

And using the Workbook_Open method in the ThisWorkbook tab in VBA to add the event automatically when the worksheet open as the below.

Dim myobject As New Class1
Private Sub Workbook_Open()
    Set myobject.appevent = Application
End Sub

Although when executed normally through debugger by just pressing F5 the code executes flawlessly.

What I'm trying to do is after the user selects an option from a data validated cell, the sheet auto updates the content viewed in that sheet and also sets some other cells in the "Lists" sheet to different values from the original ones.

So far, the code executes flawlessly when run through debugger by pressing F5, so I'm out of ideas why such an error occurs. My guess is that VBA doesn't allow changing cell values programmatically unless the sheet is selected, so how can I do that if the sheet isn't selected.

If you can please kindly help. Thanks.

You are using Range(...) and Cells(...) without specifying the Sheet. It is not good practice to use .Select to set a reference object, but prefer to set the object explicitly.

Sub test()
    Dim j As Integer
    j = 2
    If ActiveSheet.Name = "Email" Then
        With Worksheets("Lists")
         .Range("Z2:Z31").value = ""
         For i = 2 To 190
 'IN NEXT LINE the Cells(2,2).Value comes form ActiveSheet... 
 'is this you need or is a mistake? check it.
             If .Cells(i, 8).value = Cells(2, 2).value Then
                 .Cells(j, 26).value = .Cells(i, 6).value
                 j = j + 1
             End If
         Next
  'I PUT A DOT in Ranges... Is this you need Or have to refer the ActiveSheet?
         .Range("C12:O12").AutoFilter 1, ">=" & .Range("B1").value, xlAnd, "<=" & .Range("C1").value
        End With
    End If
End Sub

In any case all Range(...) and Cells(...) need a parent, and: if the code lives in a sheet, the parent is the sheet ELSE you have to define it somehow. You do it with Sheet("...").Select. So the Cells(2, 2).value and Range("C12:O12").AutoFilter ..Range("B1").value.. Range("C1").value... comes from sheet "Lists".

'This code in Email sheet module. Also move the Sub test() here.
'You don't need a Class module...
Private Sub Worksheet_Change(ByVal Target As Range)
   Call test
End Sub


Private Sub test()
   '.......
   '.......
End Sub