Wildcards and variables

[rmwin]

Is there a way to use wildcards and variables?

Example

Dim name as variant
With Worksheets(1)
.Range("A1:C" & .Range("A" & Rows.Count).End(xlUp).Row).AutoFilter
.Range("A1:C" & .Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=name

.Range("A1:C" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Worksheets(2).Range("A" & Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row).Offset(1)
.AutoFilterMode = False
End With
Application.CutCopyMode = False

I want to use the wildcard option in place of Criteria1:=name
as follows:

Criteria1:="=*ball*"

whereby the user has defined the following string to the variable name:

name="ball"

Now, here is the hard part, how do I use wildcard caracters and the variable
name (that has the string "ball" associated with it)?

 

[Norie]

Like this perhaps?

.Range("A1:C" & .Range("A" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:="=*" & name & "*"

 

[rmwin]

wildcards and variables

Thanks Norie,

That worked perfectly!