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

Question

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 1

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.

Solution 2

You haven't qualified the sheet name. So the copy happens on the ActiveSheet it then tries to reference Cell(1,1) from the ActiveSheet but on sheet Y:

Sheets("Y").Range(Cells(1, 1))

Sheets("Y") is sheet Y. Cells(1,1) is the ActiveSheet.

The copy only works because you activate sheet X first. Remove that line, select another sheet and it will fail on that as well.

Comments

[BuckTurgidson]

'The copy only works because you activate sheet X first.' I'm quite puzzled since I activate sheet "X" and "A1" refers (and works!) on sheet "Y". Any idea if Microsoft publishes any documentation that explains how this works on the background?

[Darren Bartrup-Cook]

Yes, Sheets("Y").Range("A1") works as it refers to A1 on Sheet Y - it's all one reference. Sheets("X").Range(Cells(1, 1), Cells(1, 30)) uses three references - Sheets("X").Range( references Sheet X while Cells(1, 1) and Cells(1, 30) reference the ActiveSheet. To get those to reference Sheet X as well you'd write Sheets("X").Range(Sheets("X").Cells(1, 1), Sheets("X").Cells(1, 30)) - but then it will look at the active workbook so you need to qualify that as well - ThisWorkbook.Sheets("X").Range(ThisWorkbook.Sheets("X").Cell‌​s(1, 1), ThisWorkbook.Sheets("X").Cells(1, 30))

[Darren Bartrup-Cook]

That last bit of code in my previous comment gets very clunky - which is why there's With...End With blocks. msdn.microsoft.com/en-us/library/wc500chb(v=vs.90).aspx or using variables: Set wrkSht = ThisWorbook.Worksheets(X) and wrksht.Range(wrksht.Cells(1, 1), wrksht.Cells(1, 30))