copy rows to a new worksheet VBA

Question

I am trying to write a script which copies a row from Sheet 1 to Sheet 2, if the value for the first column of Sheet 1 is greater or equal to 10.

Sub Macro1()

Cells(1, 1).Select
For i = 1 To ActiveCell.SpecialCells(xlLastCell).Row

    Cells(i, 1).Select

    If ActiveCell.Value >= 10 Then
        Rows(ActiveCell.Row).Select

        Rows(i & ":").Select
        Selection.Copy

        Sheets("Sheet2").Select
        ActiveSheet.Paste

        Sheets("Sheet1").Select

     End If

Next i

End Sub

Answer 1

This is similar to the first answer, but a few differences. Here's some notes:

• Use a for-each loop to go through a range (it's not as fast as using a variant array, but keeps things simple and offers better speed than a for loop.
• You may want add a "If IsNumeric(cell)" check before the value check.
• Don't use select - you don't need to and it wastes resources.
• Better to use the last cell used in A then the used range.

Here is the code:

Sub CopyRows()

Dim cell As Range
Dim lastRow As Long, i As Long

lastRow = Range("A" & Rows.Count).End(xlUp).Row
i = 1

For Each cell In Sheets(1).Range("A1:A" & lastRow)
    If cell.Value >= 10 Then
        cell.EntireRow.Copy Sheets(2).Cells(i, 1)
        i = i + 1
    End If
Next

End Sub