asked on

Copy To Next Blank Line

I get a runtime error 1004 “Select method of Range class failed” on this line.
Cells(NextRow, 1).Select

I have included a file.

Any help would be appreciated.

Public Sub CopyRows()
   Sheets("Sheet1").Select
    ' Find the last row of data
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' Loop through each row
    For x = 2 To FinalRow
        ' Decide if to copy based on column H
        ThisValue = Cells(x, 8).Value
        If ThisValue = "ir" Then
            Cells(x, 1).Resize(1, 8).Copy
            Sheets("a").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        ElseIf ThisValue = "RR" Then
            Cells(x, 1).Resize(1, 33).Copy
            Sheets("b").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("Sheet1").Select
        End If
    Next x

End Sub

Private Sub CommandButton1_Click()
CopyRows
End Sub

Open in new window

Copy-NextBlankRow.xls

Norie

All that Selecting isn't making it clear what's active when.

Try this which basically does the same but doesn't use Select or Activate.

Option Explicit

Public Sub CopyRows()
Dim wsSrc As Worksheet
Dim wsA As Worksheet
Dim wsB As Worksheet
Dim NextRow As Long
Dim FinalRow As Long
Dim X As Long
Dim ThisValue

    Set wsSrc = Sheets("Sheet1")

    Set wsA = Sheets("A")
    Set wsB = Sheets("B")

    FinalRow = wsSrc.Cells(Rows.Count, 1).End(xlUp).Row

    ' Loop through each row
    For X = 2 To FinalRow
        ' Decide if to copy based on column H
        ThisValue = wsSrc.Cells(X, 8).Value

        If ThisValue = "ir" Then

            NextRow = wsA.Cells(Rows.Count, 1).End(xlUp).Row + 1

            wsSrc.Cells(X, 1).Resize(1, 8).Copy wsA.Cells(NextRow, 1)
        ElseIf ThisValue = "RR" Then
            NextRow = wsB.Cells(Rows.Count, 1).End(xlUp).Row + 1

            wsSrc.Cells(X, 1).Resize(1, 33).Copy wsB.Cells(NextRow, 1)


        End If
    Next X

End Sub

Private Sub CommandButton1_Click()
    CopyRows
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

grogman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ca1358

ASKER

Thank you

Norie

Sorry if the code I posted was a bit too much, but you really should avoid using Select.