I have found a useful vba (code below) from Ron de Bruin and satisfy my
needs. But the code only solve the one filter criteria. I copied the
following code as many as possible, it is too clumsy. Is there any
method, eg do loop, to solve many filter criteria? In my case, I have
more than 20 criteria.

Sub Copy_With_AutoFilter()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim Str As String
Sheets("Working").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="MyRange",
RefersToR1C1:="=Working! R1C1:R20000C11"

Set WS = Sheets("Working")
Str = "COE"
With WS.Range("MyRange")
..AutoFilter Field:=4, Criteria1:=Str
Set WSNew = Sheets.Add
..Cells.SpecialCells(xlCellTypeVisible).Copy
WSNew.Range("A1")
End With

WS.AutoFilterMode = False
On Error Resume Next
WSNew.Name = Str
If Err.Number 0 Then
MsgBox "Change the name of : " & WSNew.Name & " manually"
Err.Clear
End If
On Error GoTo 0

TIA

norika


--
norika
------------------------------------------------------------------------
norika's Profile: http://www.excelforum.com/member.php...fo&userid=4878
View this thread: http://www.excelforum.com/showthread...hreadid=375507