Wants to filter and copy from sheet2 but without activating sheet2

[NileshAPatel]

Dear Team,

I have a excel file wherein 2 sheets are their.
From Sheet1, wants to filter data in sheet2 table and copy and paste filtered data in sheet1 A1 cell.
Is is possible get it done through sheet1 itself without activating or selecting sheet2

Thanks

 

[NileshAPatel]

I tried as below , but filter get apply only when I run it after selecting sheet2 not from sheet1.
Can't understand why filter doesn't apply when I run it from sheet1.


Sub Test()
On Error Resume Next
With Sheets("Sheet2")
.ShowAllData
.ListObjects("Table1").Range.AutoFilter Field:=(Rows("2").Find("Model").Column), Criteria1:= _
"=DZIRE", Operator:=xlOr, Criteria2:="=Ertiga"

End With

Range("Table1[Outlet Name],Table1[Supplier Category],Table1[Model]").Copy


ActiveSheet.Paste


End Sub


Thanks a lot