Run time error '91' for copying rows to another sheet

Question

Sub retrieve()

Dim r As Long, endrow As Long, pasterowindex As Long, Cells() As String, Columns As Range

Sheets("Raw Trade Log").Range("A4").Select
Selection.End(xlDown).Select: endrow = ActiveCell.Row

pasterowindex = 1

For r = 4 To endrow
    If Cells(r, Columns(17).Value = "Y") Then

        Rows(r).Select
        Selection.Copy

        Sheets("Completed Trade log").Select
        Rows(pasterowindex).Select
        ActiveSheet.Paste

        pasterowindex = pasterowindex + 1

        Sheets("Raw Trade Log").Select
    End If
Next r


End Sub

I am trying to tell vba to automatically copy the whole row to another sheet when value in a column becomes "Y" however I keep getting

Run time error '91'

from If Cells(r, Columns(17).Value = "Y") Then and I have not idea how to fix it, can someone kindly let me know where did I made a mistake?

Try to avoid using the words `Select` and `Activate` in the whole code. Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba and rewrite sub. Then the error should not exist. — Vityata, May 17 '18 at 16:42

score 1 · Accepted Answer · answered May 17 '18 at 16:48

The error is mainly because of the Select and the Activate words. These are really not programming-friendly and one should be careful around them. Thus, the best way is to avoid them completely - How to avoid using Select in Excel VBA.

Concerning the task "How to copy rows under some condition to another worksheet" this is a small example, without the Select and Activate:

Sub TestMe()

    Dim wksTarget As Worksheet: Set wksTarget = Worksheets(1)
    Dim wksSource As Worksheet: Set wksSource = Worksheets(2)
    Dim r As Long

    For r = 4 To 50
        If wksSource.Cells(r, "A") = "y" Then 
            wksSource.Rows(r).Copy Destination:=wksTarget.Rows(r)
        End If
    Next r

End Sub

the 50 is hardcoded, it can be referred as a variable as well;
the code checks for the word y in column A, but it can be changed by changing the A in If wksSource.Cells(r, "A") to something corresponding.

score 0 · Answer 2 · answered May 17 '18 at 16:59

you could use AutoFilter():

Sub retrieve()
    With Sheets("Raw Trade Log") 'reference your "source" sheet
        With .Range("A3", .Cells(.Rows.Count, 1).End(xlDown)).Offset(, 16) ' reference referenced sheet column Q cells from row 3 (header) down to column A last not empty row
            .AutoFilter Field:=1, Criteria1:="y" ' filtere referenced column with "y" content
            If Application.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count - 1, .Columns.Count).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Completed Trade log").Range("A1") ' if any filtered cell other than header, copy filtered cells entire row to "target" sheet
        End With
        .AutoFilterMode = False
    End With
End Sub

Run time error '91' for copying rows to another sheet

2 Answers2