Copy and Paste

[Paleo]

I have a problem. I need to look upon a worksheet and find cells that have a "JF" expression on them. After that I need to select their hole row, cut them, create a new worksheet and paste the cutted cells on it.

I did it till the cut and sheets.add phase but I am not being able to paste the content.

May someone help me please?????

 

[Paleo]

Bumping it up!!

 

[Zack Barresse]

If, in a blank column, you put in this formula ..


=LEN(C1)


.. and copy down, does it have anything other than 2 in that column?

 

[Paleo]

Hi Zack,

nope all the rows return me 2. No empty rows, nor rows greater than 2. My worksheet has 27,662 rows, is it an issue?

 

[Zack Barresse]

Oh my gosh. Okay, I goofed. When deleting you need to go from the bottom UP, not the other way around. This will skip some values. Try something like this ...


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

<SPAN style="color:#00007F">Sub</SPAN> TransferDataPlease()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    n = 1
    <SPAN style="color:#00007F">For</SPAN> i = Range("C65536").End(xlUp).Row <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">If</SPAN> Range("C" & i).Value = "NJ" <SPAN style="color:#00007F">Then</SPAN>
            Range("C" & i).EntireRow.Copy Sheets("One").Range("A" & n)
            n = n + 1
            Range("C" & i).EntireRow.Delete
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Sorry 'bout the confusion.

 

[Paleo]

Great Zack, ray: ray: ray:

this time really worked just FINE!!!

THANKS A LOT!!! YOU ARE "THE" GURU here!!!! ray: ray: ray:

 

[Zack Barresse]

Great Zack, ray: ray: ray:

this time really worked just FINE!!!

Sorry 'bout the confusion. And I know better too.

THANKS A LOT!!! YOU ARE "THE" GURU here!!!! ray: ray: ray:

There are a lot better than I roaming these parts.

 

[natedogg]

i am trying to attempt to make it copy all BOLD in column A and send it to another sheet, wont post the code cause it is wrong, trying
.font.bold = true Then
can it be as easy as taking out the code for deleting to make this not delete? thank you

 

[Zack Barresse]

Yes, should be. Try this ...


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

<SPAN style="color:#00007F">Sub</SPAN> TransferDataPlease()
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    n = 1
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Range("A65536").End(xlUp).row <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">If</SPAN> Range("A" & i).Font.Bold = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN>
                                    <SPAN style="color:#007F00">'** \/ Change sheet name \/ **'</SPAN>
            Range("A" & i).EntireRow.Copy Sheets("Sheet2").Range("A" & n)
            n = n + 1
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Paleo]

Hi Zack,

I am back.

Now it's working just fine but a bit slow. About 3 minutes in a 512 Mb RAM machine, so I had an idea .

Would be faster if we just classify by alphabetic order column A first and then cutted the rows with NC in, paste them in another worksheet (let's call it "new". A very criative name ) and then we delete those rows from the original worksheet?

The way we did the macro has to read every row and they are a lot and this way would be just to select a range of consecutive cells, copy and paste them.

What do you think?

I tryied this yet but didnt suceed. May you help me out?

 

[Zack Barresse]

Now that is an excellent idea!


...


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

<SPAN style="color:#00007F">Sub</SPAN> TransferNJvals()
    <SPAN style="color:#00007F">Dim</SPAN> filterRng <SPAN style="color:#00007F">As</SPAN> Range, copyRng <SPAN style="color:#00007F">As</SPAN> Range
    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> filterRng = Range("B1", Range("E65536").End(xlUp))
    filterRng.AutoFilter field:=2, Criteria1:="=NC"
    filterRng.SpecialCells(xlCellTypeVisible).Copy Sheets("One").Range("A1")
    Range(filterRng.Cells(2, 1), filterRng.Cells(filterRng.Rows.Count, _
        filterRng.Columns.Count)).SpecialCells(xlCellTypeVisible).Delete
    filterRng.AutoFilter
    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>