[VBA] Autofilter Criteria

[arun0075]

Hi, I am using excel 2007 and for my job i am given bunch of data in excel which i have to sort and provide with report kind of format.

So, the data I am working consists of headers like:
1. Date
2. Product
3. Product Unit
4. Price / Unit
5. Customer
6. Customer Country
7. Supplier (because some product is made by us and others are outsourced)
8. Supplier Country

And as our product list deals with many items, and I am required to sort item wise in different sheet, so I was earlier doing it manually, but now I since its becoming a daily work I wish to develop a VBA code to handle this.

So, I am filtering the data and copying in their respected sheet. For this the code I am using is as below:

Worksheets("Data").Activate
n = Application.Count(Sheets("Data").Range("B:B"))
Worksheets("Cln").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$" & n + 1).AutoFilter Field:=12, Criteria1:= _
"=*viet*", Operator:=xlAnd
ActiveSheet.Range("$A$1:$N$" & n + 1).AutoFilter Field:=3, Criteria1:= _
"=*Berry*", Operator:=xlOr, Criteria2:="=*MRS*"
Range("A1:N1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Worksheets("VMRS").Activate
Sheets("VMRS").Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Worksheets("Cln").Activate
Selection.AutoFilter
Worksheets("Cln").Range("A1").Select

However, for this Product I know that this is purchased from outside country Vietnam so I had put the filter of country to be vietnam. and product name consists like: Berry, MRS, VOMN. So, in filter i can only use 2 criteria. Is there any other way to work with it without getting criteria restriction?

Also, I am copying the whole filtered page from header. Is there any way to just copy the filter result and paste it below the header on that product sheet or last used row?

Thank you.

 

[jeffreybrown]

Although not specifically a rule here, it is preferred if you are going to cross post you provide a link to the cross post. x-post

 

[arun0075]

My sorry, I did cross post and didnt provide with link. Thanks for the acknowledgement.