Copy and Paste

Hello Paleo, welcome to the board!!


So when a cell has the value JF, copy/paste whole row to a new worksheet, delete the original row? Is that the only value in a cell, "JF"? If not, can you give examples?

Hi Zack,

yes "JF" is the only value in the cell. I tryied like this:


but didnt work out either.

May you help me please?

Bumping it up´!

Close. Although I'm not sure if you are looking for JF or NJ?? This looks for NJ in any cell in column A of the activesheet...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> fooTest()
    <SPAN style="color:#00007F">Dim</SPAN> RowNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Cell <SPAN style="color:#00007F">As</SPAN> Range, Rng <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Set</SPAN> Rng = Range("A65536").End(xlUp)
    RowNumber = 2
    <SPAN style="color:#00007F">If</SPAN> Rng.Row < 2 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cell <SPAN style="color:#00007F">In</SPAN> Range("A1", Rng)
        <SPAN style="color:#00007F">If</SPAN> Cell.Value = "NJ" <SPAN style="color:#00007F">Then</SPAN>
            Cell.EntireRow.Copy Sheets("One").Range("A" & RowNumber)
            Cell.EntireRow.Delete
            RowNumber = RowNumber + 1
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Does this help?

Gee Zack, ray:

this was EXACTLY what I needed. I THANK YOU, VERY MUCH. You saved my day!! ray:

Well I am back here,

the problem wasnt solved yet. It is working but in a strange way. It copies some rows (not all) and deletes some rows also (not all also). Yes, it deletes the rows it has copied.

I tried breaking the routines apart. One for copy purpose and another for deletion but simply got the same result. Thought it was a memory issue and changed from a 512 Mb machine to a 1 Gb machine. Same result.

So now, may someone help me please????

I would hesitate a guess right off that the cell value does not match that of the 'hardcoded' VBA value ("NJ"). Probably due to leading/trailing spaces. You can perform a simple test for this by changing this line of code ...

.. with this line of code ...

It should look at a non-case sensitive trimmed (no leading/trailing spaces) value and test against "NJ". Does that make a difference? If not, post a small (but various) representation of your data using the HTML Maker.

Hi Zack,

well, didnt work again. Same old problem. I am posting my code here:

I had to change the text from "NJ" to "NC" but thats not the problem, just a little adjust.

Please may you help me?