SORT,COPY AND PASTE

[verluc]

I want to do the following via VBA :
I have in sheet1,in column M sometimes the number "1" in the range M1:M1000
If there is a number "1" then copy and paste the cell from column K to sheet2
Any idea?
Many thanks

 

[Von Pookie]

Try:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> CopyCells()
<SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range

<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> [M1:M1000]
    <SPAN style="color:#00007F">If</SPAN> cell.Value = 1 <SPAN style="color:#00007F">Then</SPAN> cell.Offset(0, -2).Copy Destination:= _
        Sheets("Sheet2").[A65536].End(xlUp).Offset(1, 0)
<SPAN style="color:#00007F">Next</SPAN> cell

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>