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

Are you asking for multiple filter criteria and/or filter fields?

In any event both can be set by passing values to the autofilter method.
You need to decide where the values are stored. For example you might store
the values in a worksheet range, then you will need to loop through the
range and pass the criteria and field values.

Here is an example of a procedure that is called from the main program to
set a filter up, the criteria is passed in the Value1 string, eg
in main program use....
Call ReadData("36")

' the sub procedure to set filter......
Sub ReadDatal(Value1 As String)
' reset filter if filter on
data.Range("A8:L8").AutoFilter

' turn on filter if not on already on
If Not data.AutoFilterMode Then data.Range("A8:L8").AutoFilter

' set value1 filter if a value1 exists
If Len(Trim(value1)) 0 Then
data.Range("A8:L8").AutoFilter Field:=2, Criteria1:=value1
End If
End Sub

--
Cheers
Nigel



"norika" wrote in
message ...

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