jeudi 5 mars 2020

How to use if to autofilter then copy&paste to another worksheet

I would like to use if statement to filter "CWO" in worksheets("DATA") then copy all data and paste them into worksheets("CRO"). If there is no "CWO" then don't copy. Here's my code but it keeps ShowAllData in worksheets("Data") eventhough there is "CWO" in worksheets("DATA")

Sub Copy_CRO() dim ldatarow as long
dim lcworow as long

ldata= Worksheets("DATA").Cells(Worksheets("DATA").Rows.Count, "A").End(xlUp).Row

lcworow = Worksheets("CRO").Cells(Worksheets("CRO").Rows.Count, "A").End(xlUp).Row
lcworow = lcworow + 1
if Find = Worksheets("DATA").Range("A1:A" & ldatarow).AutoFilter (Field:=1, Criteria1:="=CWO") then

Worksheets("DATA").Range("B2:B" & ldatarow).Copy
Worksheets("CRO").Range("A2:A" & lcworow).PasteSpecial

Else: worksheets("DATA").ShowAllData

Aucun commentaire:

Enregistrer un commentaire