I am writing some vb code to help my friends dad with excel and I'm having a problem with one part. Basically my code checks if the value in certaing column's is = 0 or > 1 for each row then if it's true it select the entire row, copies it and is supposed to paste it on the next sheet. However, I can't figure out the selection and pasting. I have the copying part down but need help with the rest. my code goes something like this:

for i=2 to 50000
    cola=cells(i,1).value
    colu=cells(i,17).value
    colq=cells(i,21).value
    if (cola > 0 and colu=0 and colq=0) then
     'the copying is done somehow but since im not at the computer with the code this part might be wrong
     entirerow.copy
     sheets("sheet2").activate
     'heres where i need help
      cells(j,1).select
      selection.paste
      j=j+1
      sheets("sheet2").activate
     end if
next i

I have every other part done so this all I need. any help would be greatly appreciated

Sub CopyData()
Application.ScreenUpdating = False
Dim dLoop As Double
Dim dRow As Double
' Set starting value for row counter on sheet 2, adjust as required
dRow = 1
For dLoop = 2 To 50000
    If Cells(dLoop, 1) > 0 And Cells(dLoop, 17) = 0 And Cells(dLoop, 21) = 0 Then
        ' Copy Data
        Rows(dLoop & ":" & dLoop).Select
        Selection.Copy
        ' Paste Dats
        Sheets("Sheet2").Select
        Range("A" & dRow).Select
        ActiveSheet.Paste
        ' Return to original sheet
        Sheets("Sheet1").Select
        ' Increment Row counter on second sheet
        dRow = dRow + 1
    End If
Next iLoop
Application.ScreenUpdating = True
End Sub

Thanks!

Taking this on a bit further, another way could be to insert the copied row into row 2 of sheet 2 with all other rows being pushed down one line, 6 of one half a dozen of another?

Sub CopyData()
Application.ScreenUpdating = False
Dim dLoop As Double
For dLoop = 2 To 50000
    If Cells(dLoop, 1) > 0 And Cells(dLoop, 17) = 0 And Cells(dLoop, 21) = 0 Then
        ' Copy Data
        Rows(dLoop & ":" & dLoop).EntireRow.Copy
' Selection.Copy
        Sheets("Sheet2").Select
        Range("2:2").Insert Shift:=xlDown
        ' Return to original sheet
        Sheets("Sheet1").Select
    End If
Next iLoop
Application.ScreenUpdating = True
End Sub


cheers

Matt

when it gets to
        Range("A" & dRow).Select
it says "select method of range class failed

hmm, the code works fine for me (well, with one small change - change Next iLoop to read Next dLoop) but that wouldn't cause your problem..

Can you put a breakpoint in the code at that point and then check the value of the following elements in the immediate window (type as shown)

?dRow
?vartype(dRow)
?dLoop
?vartype(dLoop)
?activesheet.name

I don't quite understand what you are asking in the last part, and just so you know when i type it in t changes by itself to Print (everything) instead of ?(everything)

sorry, it changed to print beacuse you typed it in the code window, should have said I wanted you to type in the immediate window.

Click to the left of the line with the problem to set a breakpoint (the line will turn brown and a brown circle will appear in the left margin).

Now run the code as normal, it will stop when it reaches that point.
Open the immediate window if not already open (Ctrl + G)
Now type each of those elements and see what results you get.

this is what i got
?dRow
 1
?vartype(dRow)
 5
?dLoop
 2
?vartype(dLoop)
 5
?activesheet.name
Sheet2

ok, so it appears to die on the first pass..

Is either sheet protected?
Are the cells you are copying from or to merged at all?