Excel VBA Run time Error 424: Object Required

Question

I am totally new in VBA and coding in general, I'm trying to get data from cells from the same workbook if the condition is met to paste in another book.

I am getting this error when trying to get values entered in excel cells:

Run Time Error '424' object required

When I'm pressing the debug button it takes to the first line and highlights it. I am not able to recognize why it is happening? Also when in watch window it shows a=0.

The code is

Sub copycells()
    a = ThisWorkbook.Worksheets("Sale").Cells(Row.Count, 2).End(xlUp).Row
    For i = 2 To a
    If Worksheets("Sale").Cells(i, 6).Value = "Parmesh" Then
    Worksheets("Sale").Row(i).copy
    Workbooks("Source.xlsx").Worksheets("Billdetails").Activate
    b = Workbooks("Source.xlsx").Worksheets("Billdetails").Cells(Row.Count, 2).End(xlUp).Row
    Workbooks("Source.xlsx").Worksheets("Billdetails").Cells(b + 1, 1).Select
    ActivateSheet.Paste
    Worksheets("Sale").Activate
    End If
    Next
    Application.CutCopyMode = False
    Workbooks("Purchase.xlsx").Worksheets("Sale").Cells(1, 1).Select
    End Sub

Answer 1

Option Explicit: Your Friend

That should teach you to always use Option Explicit. If you had been using it the following might have happened (Compile error: Variable not defined):

After OK:

you see that something is wrong with Row.

After changing to Rows another Compile error: Variable not defined. After OK:

you see that something is wrong with a =.

After adding Dim a As Long another Compile error: Variable not defined. After OK:

you see that something is wrong with i.

After adding Dim i As Long another Compile error: Variable not defined. After OK:

You see something is wrong with Row again.

After changing to Rows another Compile error: Variable not defined. After OK:

you see that something is wrong with b =.

After adding Dim b As Long another Compile error: Variable not defined. After OK:

you see that something is wrong with ActivateSheet.

After changing to ActiveSheet finally a Run-time error:

and after Debug:

Row looks suspicious again.

After changing to Rows another Run-time error:

and after Debug:

you see that something is wrong with ActiveSheet.Paste, especially Paste.

After changing to ActiveSheet.PasteSpecial another Run-time error:

and after Debug:

you see something is wrong with Worksheets("Sale").Activate.

Since Source.xlsx is active you consider changing to Workbooks("Purchase.xlsx").Worksheets("Sale").Activate and everything's finally OK. Or is it?

The Code

Option Explicit

Sub copycells()
    Dim a As Long
    Dim b As Long
    Dim i As Long
    With ThisWorkbook.Worksheets("Sale")
        a = .Cells(.Rows.Count, 2).End(xlUp).Row
        For i = 2 To a
            If .Cells(i, 6).Value = "Parmesh" Then
                .Rows(i).Copy
                With Workbooks("Source.xlsx").Worksheets("Billdetails")
                    b = .Cells(.Rows.Count, 2).End(xlUp).Row
                    .Cells(b + 1, 1).PasteSpecial
                End With
            End If
        Next
        ' If Purchase.xlsx and ThisWorkbook are the same then use the following:
        '.Cells(1).Select
    End With
    Application.CutCopyMode = False
    ' If Purchase.xlsx and ThisWorkbook are not the same then use the following:
    'Workbooks("Purchase.xlsx").Worksheets("Sale").Cells(1, 1).Select
End Sub


' Assuming that you need only values and that "Thisworkbook" is "Purchase.xlsx"
Sub copyCellsValues()

    Const SourceBook As String = "Source.xlsx"
    Const SourceSheet As String = "Billdetails"
    Const MainSheet As String = "Sale"
    Const MainColumn As Long = 6
    Const Criteria As String = "Parmesh"

    Dim Sale As Worksheet
    Dim Bill As Worksheet
    Dim a As Long
    Dim b As Long
    Dim i As Long

    Set Sale = ThisWorkbook.Worksheets(MainSheet)
    Set Bill = Workbooks(SourceBook).Worksheets(SourceSheet)

    a = Sale.Cells(Sale.Rows.Count, 2).End(xlUp).Row
    b = Bill.Cells(Bill.Rows.Count, 2).End(xlUp).Row + 1

    For i = 2 To a
        If Sale.Cells(i, MainColumn).Value = Criteria Then
            Bill.Rows(b).Value = Sale.Rows(i).Value
            b = b + 1
        End If
    Next

End Sub

Answer 2

First of all, it seems you need to declare missed variable:

Sub copycells()
    Dim a as Integer
    Dim b as Integer
    a = ThisWorkbook.Worksheets("Sale").Cells(Row.Count, 2).End(xlUp).Row
    For i = 2 To a
      If Worksheets("Sale").Cells(i, 6).Value = "Parmesh" Then
         Worksheets("Sale").Row(i).copy
         Workbooks("Source.xlsx").Worksheets("Billdetails").Activate
         b = Workbooks("Source.xlsx").Worksheets("Billdetails").Cells(Row.Count, 2).End(xlUp).Row
         Workbooks("Source.xlsx").Worksheets("Billdetails").Cells(b + 1, 1).Select
        ActivateSheet.Paste
        Worksheets("Sale").Activate
     End If
   Next
   Application.CutCopyMode = False
   Workbooks("Purchase.xlsx").Worksheets("Sale").Cells(1, 1).Select
End Sub

Also, you may try to use the Set operator.

Anyway, it is not clear what property or method gives an error. So, I'd recommend breaking the chain of property and method calls:

a = Workbooks("Purchase.xlsx").Worksheets("Sale").Cells(Rows.Count, 2).End(xlUp).Row

Just declare separate lines of code where a single property or method will take its place. For example:

Dim workbk as Excel.Workbook
Dim worksht as Excel.Worksheet

Set workbk = Workbooks("Purchase.xlsx")
Set worksht = workbk.Worksheets("Sale")

Following that way, you will be able to find the problematic call.