Application-defined or object-defined error

[cellist]

I want to use the code shown below to gain a better understanding of
how copy and paste work in VBA. When I run the code it gives me:
Run-time error '1004'
Application-defined or object-defined error
when it tries to execute
Cells(NextRow, 1).Select
At that point NextRow value is 6

Public Sub CopyRows()
    Sheets("SOURCE").Select
    Dim FinalRow As Long
    Dim x As Long
    Dim ThisValue As String
    Dim NextRow As Long
    ' 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 D
        ThisValue = Cells(x, 4).Value
        If ThisValue = "A" Then
            Cells(x, 1).Resize(1, 33).Copy
            Sheets("sheetA").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Debug.Print "nextrow " & NextRow
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("SOURCE").Select
        ElseIf ThisValue = "B" Then
            Cells(x, 1).Resize(1, 33).Copy
            Sheets("sheetB").Select
            NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
            Sheets("SOURCE").Select
        End If
    Next x
End Sub

The code is published at
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-copy-method-excel
Ironically the code was provided by Bill Jelen,MrExcel.com!

The only intentional changes I made to the posted code (as far as I know) are: added Option Explicit; add Dim for variables; added a debug.print; changed the name of the source worksheet from sheet1 to SOURCE.

Please let me know how to avoid the error.
Thanks.

 

[cellist]

Re: help with Application-defined or object-defined error

I think the IMG should have been attachments. I went in to edit but 10 minutes has passed. I don't see how to attach. Apparently, I'm not permitted to attach. How do I get permission?

 

[cellist]

Re: help with Application-defined or object-defined error

@Peter_SSs
maybe this will work
https://drive.google.com/open?id=0B1FSmzIGFwq0NkhBVXRfcl9BOTA
is this what you were expecting?

 

[Peter_SSs]

Re: help with Application-defined or object-defined error

is this what you were expecting?

No, I would have expected headings in row 1 of the 'SOURCE' sheet. How does the code work if you have that?

 

[cellist]

Re: help with Application-defined or object-defined error

No, I would have expected headings in row 1 of the 'SOURCE' sheet. How does the code work if you have that?

see:
https://drive.google.com/open?id=0B1FSmzIGFwq0MmFaVm51TUN4eUE

It doesn't copy anything. I must have put the criteria in the wrong column. Does
it need to say .AutoFilter Field:=4? (duh?)

 

[Peter_SSs]

Re: help with Application-defined or object-defined error

I must have put the criteria in the wrong column.

No, I had my criteria in the wrong column. Sorry about that.

Does it need to say .AutoFilter Field:=4? (duh?)

Yes, that is exactly what it should say - in both places.

 

[cellist]

Re: help with Application-defined or object-defined error

No, I had my criteria in the wrong column. Sorry about that.
Yes, that is exactly what it should say - in both places.

I changed it to
.AutoFilter Field:=4, Criteria1:="A"
.AutoFilter Field:=4, Criteria1:="B"

see:
https://drive.google.com/open?id=0B1FSmzIGFwq0NkhBVXRfcl9BOTA

Thanks.

 

[Peter_SSs]

Re: help with Application-defined or object-defined error

Looks like the code worked exactly as expected to me. Your SOURCE sheet this time has nothing in column E yet your SheetA and SheetB do. Those rows in Sheet A & B with data in column E came form the previous version of the code that you had run. This time the code has added 2 'A' rows and 1 'B' row below that data. The original code from the Microsoft site that you linked to in post no. 1 also just added rows below any existing data on the 'A' and 'B' sheets and so did Norie's from post no. 3, so I took the same approach.

 

[cellist]

Re: help with Application-defined or object-defined error

Looks like the code worked exactly as expected to me. Your SOURCE sheet this time has nothing in column E yet your SheetA and SheetB do. Those rows in Sheet A & B with data in column E came form the previous version of the code that you had run. This time the code has added 2 'A' rows and 1 'B' row below that data. The original code from the Microsoft site that you linked to in post no. 1 also just added rows below any existing data on the 'A' and 'B' sheets and so did Norie's from post no. 3, so I took the same approach.

Yes, I think the code is now working as expected.
I added the "beginning content of sheetB" in column E to document what was already in the sheet before running the code.
Thanks.

 

[Peter_SSs]

Re: help with Application-defined or object-defined error

Yes, I think the code is now working as expected.

Good news.