Many thanks its works

But I counted the apple in the data there where 15 row and its only filtered
12

Is there way to extend the range to read from a1:A5000

Thanks for your help

"JLatham" wrote:

Just keep 'repeating' one of the copying sections of the code, as:

Selection.AutoFilter field:=1, Criteria1:="banana"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add.Name = "banana"
ActiveSheet.Paste
ActiveSheet.Next.Select
ActiveSheet.ShowAllData

and insert it just before the Application.CutCopyMode= False statement.
Change "banana" to whatever you need the new criteria to be.


"George A. Jululian" wrote:

Many Thanks it works

but how can i amend the VBA to do more then two filters

George

"muddan madhu" wrote:


create a header row for your data

and then run the macro

Sub separate()
Range("A1").Select
Selection.AutoFilter field:=1, Criteria1:="apple"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add.Name = "apple"
ActiveSheet.Paste
ActiveSheet.Next.Select
ActiveSheet.ShowAllData
Selection.AutoFilter field:=1, Criteria1:="banana"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets.Add.Name = "banana"
ActiveSheet.Paste
ActiveSheet.Next.Select
ActiveSheet.ShowAllData
Application.CutCopyMode = False
End Sub

On Feb 4, 3:54 pm, George A. Jululian
wrote:
Hi all,

I have worksheet full of data and I need your help

I Need a macro (VBA) to sort them all and to remove the entire rows for
Apple, Banana and put them in sheet separate

Apple 5 6 7 8
Banana 4 3 5 87
Appel 5 6 7 8
Appel 5 6 7 8
Banana 4 3 5 87
Appel 5 6 7 8
Appel 5 6 7 8
Orange 11 3 55 2
Appel 5 6 7 8
Appel 5 6 7 8
Orange 11 3 55 2

Please help