Search code examples
excelsyntaxcopy-pastevba

range.copy Destination "A1" vs "Cells(1,1)" Excel/VBA

Consider the below:

    Sheets("X").Activate
    Sheets("X").Range(Cells(1, 1), Cells(1, 30)).Copy Destination:=Sheets("Y").Range("A1") 'Syntax 1
    Sheets("X").Range(Cells(1, 1), Cells(1, 30)).Copy Destination:=Sheets("Y").Range(Cells(1, 1)) 'Syntax 2

Why does Syntax 1 works while Syntax 2 runs into 'Application-defined or object-defined error'?

Solution

  • Unqualified Cells(1,1) belongs to the ActiveSheet, which is currently Sheets("X"), so it does not belong to Sheets("Y").

    OTOH: this should work:

    Destination:=Sheets("Y").Range(Sheets("Y").Cells(1, 1), Sheets("Y").Cells(1, 1)) 
'                             ^^^^^^^^^^^^^

    Dont use unqualified ranges. Drop the Activate stuf altogether from your code.