statement not making sense

[mike]

Can somebody explain to a beginning programmer why this statement does not
work?

wkb2.Worksheets(1).Range("a1", Range("i1").End(xlDown)).Copy _
Destination:=wkb1.Worksheets(1).Range("a2")

keep getting this error message.
Run-time error '1004': application-defined or object-defined error


If I write it this way, it works.
Set selRow = wkb2.Sheets(1).Range(ActiveCell, ActiveCell.End(xlDown))
wkb2.Worksheets(1).Range("a1", "i" & selRow.Rows.Count).Copy _
Destination:=wkb1.Worksheets(1).Range("a2")

I am trying to see why the one statement does not work and the two
statements works. I am assuming that it has to do with the row count or the
last row on the "i" column is empty but the error statement doesn't suggest
that. Does it have to do with references to objects? I don't mine having two
statements but I want to understand why it doesn't work. Thanks in advance
for your explaination.

 

[Tim Williams]

In your first statement the second part of the Range() defining the area to
be copied is not qualified with a worksheet.
Unqualified ranges always refer to the active sheet, so unless the sheet
being
copied from is active the statement fails.

This change should work:

'*****************************************

Dim sht
Set sht = wkb2.Worksheets(1)

sht.Range("a1", sht.Range("i1").End(xlDown)).Copy _
Destination:=wkb1.Worksheets(1).Range("a2")

'****************************************

Tim

 

[mike]

Thank you, that worked. I guess it is better to qulify everything instead of
leaving it to chance.