Problem using "contains" in Autofilter

[MecaTron101]

I'm fairly new to VB but I'm trying to write a program that is meant to do the following;

XL file#1
Look in column D
Copy first value (M123, I use a "mid" function to extract only the numbers.)​

XL file#2

Custom filter column J (Criteria1:="contains" previous value 123)
Copy all visable rows except #1 (Contains headings w. autofilter)​

XL file#3 (Final list)

Paste copied rows
[Loop]​

The problem comes when I set up the custom filter with Criteria1 and use the "contains" setting. I need the program to use the content from another cell as the value.
Like this; Criteria1:="=*Mid(cell.Value, 2,3)*"
The problem is that I get "Mid(cell.Value, 2 ,3)" as the sorting criteria, not the actual value. I only get the value if I set Criteria1=cell.Value.

What am I doing wrong?
I've marked the affected rows red in the code below.

Sub Components()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim cell As Range

Set ws1 = Workbooks("IB1_2.xls").Sheets("IB1")
Set ws2 = Workbooks("DSI info_Indusbuild (Components FS).xls").Sheets("All components")
Set ws3 = Workbooks(ActiveWorkbook.Name).Sheets("All components")

' Loop for each value in column D
For Each cell In ws1.Range("D2", ws1.Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

' Test if value is found in ws2 column J
' Uses only numbers from ws2 column D
ws2.Range("J1").AutoFilter Field:=7, Criteria1:="=*Mid(cell.Value, 2, 3)*"
If ws2.Range("J2", ws2.Range("J" & Rows.Count)).Find(Mid(cell.Value, 2, 3), LookAt:=xlWhole, MatchCase:=False) Is Nothing Then

' No match found
MsgBox "No match found for " & cell.Value, vbInformation, "No Match"

Else

' Autofilter
ws2.AutoFilterMode = False
ws2.Range("J1").AutoFilter Field:=7, Criteria1:="=*Mid(cell.Value, 2, 3)*"

' Copy\Paste
ws2.Range("J2", ws2.Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Destination:=ws3.Range("A" & Rows.Count).End(xlUp).Offset(1)

End If

Next

' Clean up
ws2.AutoFilterMode = False

MsgBox "Copy complete.", vbInformation, "Done!"

End Sub

 

[GlennUK]

Shouldn't it be:
"=*" & Mid(cell.Value, 2, 3) & "*"