Macro for auto filter

[manojparag]

Hello Everyone,

I am writing a marco which has some data in sheet1 and a column in sheet2. I wanted that marco to work as


go to sheet2 pick the value(which is String) ---- then go to sheet1 do autofilter ----- search the string value which has taken from sheet2 ----- and then copy to sheet3.

This should repted till the sheet2 column get blank.

 

[mole999]

so you can use macro recorder to build you autofilter for sheet1

do that an post it here in code tags

then you need to tell what column you are looking at for data on sheet 2 and how that is determined

copying from sheet 1 to sheet 3 can then be done,

what you are doing is planting seeds, but we have no idea if you are growing apples or coffee beans

 

[manojparag]

so you can use macro recorder to build you autofilter for sheet1

do that an post it here in code tags

then you need to tell what column you are looking at for data on sheet 2 and how that is determined

copying from sheet 1 to sheet 3 can then be done,

what you are doing is planting seeds, but we have no idea if you are growing apples or coffee beans

Yes I can do while recoding the macro and so I did. But I want be done with using do while or other vba funtion used.

 

[manojparag]

for example: ActiveSheet.Range("$A$1:$B$14").AutoFilter Field:=2,Criteria1:="=*Lion*" _Operator:=xlAnd

then again the second piece of code will be same with different criteria..
like..
ActiveSheet.Range("$A$1:$B$14").AutoFilter Field:=2,Criteria1:="=*Tiger*" _Operator:=xlAnd
and so on ActiveSheet.Range("$A$1:$B$14").AutoFilter Field:=2,Criteria1:="=*Panther*" _Operator:=xlAnd


I wanted rather taking or recoding macro and doing the same thing again and again. Can we run a loop in side a criteria which will take automatically take lion then tiger then Panther an so on...

 

[mole999]

see thats relasesed a tiny bit of Info

Lion
Tiger
Panther

now I can see that Panther Alphabetically comes after Lion and Before Tiger, hence why I asked how you determine the order

 

[manojparag]

Yes see
Sheet 1:

Sheet 2:

 

[mole999]

best will in the world I can't view your desktop remotely