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.

 

[Peter_SSs]

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

My guess is that the problem is where you have placed the code in the vba window. It should be in a standard module, not one of the worksheet modules.

 

[Norie]

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

Does this work?

Option Explicit

Public Sub CopyRows()
Dim FinalRow As Long
Dim x As Long
Dim ThisValue As String
Dim NextRow As Long

    With Sheets("SOURCE")

        ' 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").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            ElseIf ThisValue = "B" Then
                .Cells(x, 1).Resize(1, 33).Copy Sheets("sheetB").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End If
            
        Next x
        
    End With

End Sub

 

[cellist]

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

My guess is that the problem is where you have placed the code in the vba window. It should be in a standard module, not one of the worksheet modules.

That worked. Thanks.

 

[cellist]

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

@Norie
Bedtime here but I'll try your suggested code tomorrow.
Thanks for replying.

 

[Peter_SSs]

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

That worked. Thanks.

You are welcome.
The problem is that any range references in code in a worksheet module that are not specifically referenced to another sheet, apply to the sheet the module is in. So if the code was in the 'SOURCE' sheet module and you activate (Select) say SheetA and then try Cells(6,1).Select, you are actually trying to select Cells(6,1) on the 'SOURCE' sheet which is impossible because that sheet is not the active sheet at the time.

 

[cellist]

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

@Norie

If ThisValue = "A" Then
                .Cells(x, 1).Resize(1, 33).Copy Sheets("sheetA").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            ElseIf ThisValue = "B" Then
                .Cells(x, 1).Resize(1, 33).Copy Sheets("sheetB").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End If

Yours works fine, also. Thanks.

 

[Peter_SSs]

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

You said that you were using that code just as an exercise to learn about Copy/Paste and your question was about the error and how to avoid it and that's why I gave the answer I did.
If you are in fact looking at other options for code to do that job, then I would suggest that you look at alternatives to looping through a row at a time as that can be quite a slow process if you have very many rows.
Here is one example that deals with all the "A" rows at once and similarly for "B".

Sub CopyRows_v2()
  With Sheets("SOURCE")
    With .Rows("1:" & .Cells(Rows.Count, 4).End(xlUp).Row)
      .AutoFilter Field:=1, Criteria1:="A"
      .Offset(1).Copy Destination:=Sheets("sheetA").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .AutoFilter Field:=1, Criteria1:="B"
      .Offset(1).Copy Destination:=Sheets("sheetB").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .AutoFilter
    End With
  End With
End Sub

 

[cellist]

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

You said that you were using that code just as an exercise to learn about Copy/Paste and your question was about the error and how to avoid it and that's why I gave the answer I did.
If you are in fact looking at other options for code to do that job, then I would suggest that you look at alternatives to looping through a row at a time as that can be quite a slow process if you have very many rows.
Here is one example that deals with all the "A" rows at once and similarly for "B".

Sub CopyRows_v2()
  With Sheets("SOURCE")
    With .Rows("1:" & .Cells(Rows.Count, 4).End(xlUp).Row)
      .AutoFilter Field:=1, Criteria1:="A"
      .Offset(1).Copy Destination:=Sheets("sheetA").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .AutoFilter Field:=1, Criteria1:="B"
      .Offset(1).Copy Destination:=Sheets("sheetB").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .AutoFilter
    End With
  End With
End Sub

Your previous answers were more than sufficient.
I replied to the later suggestion from another poster because it, too, adds to my understanding of how vba copy-paste works. I promised I'd try it and I did and it works.

Your present example is nice and compact and introduces even more possibilities. Now that I know the .Autofilter method exists, an msdn page explains the nuances. But I never would have guessed that there is such a method.
https://msdn.microsoft.com/en-us/vb...lter-method-excel?f=255&MSPPError=-2147217396

Thanks again for your help.

 

[cellist]

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

You said that you were using that code just as an exercise to learn about Copy/Paste and your question was about the error and how to avoid it and that's why I gave the answer I did.
If you are in fact looking at other options for code to do that job, then I would suggest that you look at alternatives to looping through a row at a time as that can be quite a slow process if you have very many rows.
Here is one example that deals with all the "A" rows at once and similarly for "B".

Sub CopyRows_v2()
  With Sheets("SOURCE")
    With .Rows("1:" & .Cells(Rows.Count, 4).End(xlUp).Row)
      .AutoFilter Field:=1, Criteria1:="A"
      .Offset(1).Copy Destination:=Sheets("sheetA").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .AutoFilter Field:=1, Criteria1:="B"
      .Offset(1).Copy Destination:=Sheets("sheetB").Cells(Rows.Count, 1).End(xlUp).Offset(1)
      .AutoFilter
    End With
  End With
End Sub

I ran your code.
Is this what you expected?

What does the .Autofilter with no arguments do?