CODEWITHSUNDEEP
vbaexcel
Isaac Tang
Isaac Tang

Reputation: 13

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

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?

Upvotes: 1

Views: 159

Answers (2)

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 0

Vityata
Vityata

Reputation: 43565

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.

Upvotes: 1

Related Questions