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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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?
 

mikeprice53

Active Member
Joined
Jan 21, 2003
Messages
297
yes the entire row that is greater than 1 in CQ range I want to copy that row completely to mikessheet...

Thanks
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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.
 

mikeprice53

Active Member
Joined
Jan 21, 2003
Messages
297

ADVERTISEMENT

unfortunately nothing ! :confused:

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

thanks for your efforts though
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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:
 

mikeprice53

Active Member
Joined
Jan 21, 2003
Messages
297
sorry should have said I tried both of them along with " If Range("CQ" & MY_ROWS).Value >= 1 Then" aswell but still nothing ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,935
Members
413,953
Latest member
Arthur1471

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
Top