Moving a range between workbooks

R

Robert

I am fairly new to macro programming, but this one really has me stumped.
In moving a range of values from Book1.xls to Book2.xls, why does one of
these work and not the other?
In each case, the application is running in Book2, using Excel 2002 sp3 on
Windows XP. Both books are open.

This works:

Range("a1", "a10").Value = Workbooks("Book1.xls") _
.ActiveSheet.Range("a1", "a10").Value

However, this fails with run-time error 1004 "Application-defined or
object-defined error":

Range(Cells(1, 1), Cells(10, 1)).Value = Workbooks("Book1.xls") _
.ActiveSheet.Range(Cells(1, 1), Cells(10, 1)).Value

Can some please explain what is happening here.
Thanks, Robert
 
T

Tim Zych

The Cells part of the macro need qualification. Without it, they refer to
the activesheet of the active workbook.

this modification should help

With Workbooks("Book1.xls").ActiveSheet
Range(Cells(1, 1), Cells(10, 1)).Value = _
.Range(.Cells(1, 1), .Cells(10, 1)).Value
End With
 
Top