copying rows that meet criteria

mikeprice53

Active Member
Joined
Jan 21, 2003
Messages
297
the code below is what I am using to try and copy every row that i is greater than 0 in column CQ ( there could be a few hundred ) but not only am i struggling to get the code sorted for "If i > 0 copy the entire row that i is on" but how to do it for the humber I am after going to the other sheet...

Sub copyrow()

Dim Rng As Range
Set Rng = Sheets("a").Range("CQ7:CQ1200")
For i = 1 To 1200
If i > 0 copy the entire row that i is on
Selection.Copy
sheets("mikessheet").select
Range("A6000").End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
Selection.PasteSpecial Paste:=xlPasteValues

Next i

End Sub

Please help my brain is racked... Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hello,

Do you mean that for every cell in CQ7 to CQ1200 that is greater than 0 you want it copied to the other sheet?
 
Upvote 0
yes the entire row that is greater than 1 in CQ range I want to copy that row completely to mikessheet...

Thanks
 
Upvote 0
Hello,

Does this code suit your needs?

Code:
Sub COPY_COLUMN_CQ()
For MY_ROWS = 7 To Range("CQ65536").End(xlUp).Row
     If Range("CQ" & MY_ROWS).Value > 1 Then
        Rows(MY_ROWS).Copy
        Sheets("mikesheet").Select
        Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Sheets("Sheet2").Select
    End If
Next MY_ROWS
Application.CutCopyMode = False
End Sub

There will need to be data in Column A or some rows may be overwritten.
 
Upvote 0
unfortunately nothing ! :confused:

It dosnt flicker or anything as in dont seem to try and look up anything ?

thanks for your efforts though
 
Upvote 0
Hello,

You will need to change Sheet2 to a (id this is the name of your sheet).

Have also noticed another change required mikesheet should be mikessheet

Sorry! :oops:
 
Upvote 0
sorry should have said I tried both of them along with " If Range("CQ" & MY_ROWS).Value >= 1 Then" aswell but still nothing ?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top