У меня работает вот так:

Код
Dim d As Range
Dim c As Range
Set d = Workbooks("Книга1.xlsx").Worksheets("Лист1").Range("A1:B10")
Set c = Workbooks("Книга2.xlsx").Worksheets("Лист1").Range("A1:B10")
c.Value = d.Value


xlsx - не стал пробовать с книгами старого формата, надеюсь, будет работать

Set - корректное присвоение ссылки на объект
Почитать можно тут
http://msdn.microsoft.com/en-us/library/aa...28VS.60%29.aspx

Абсолютные пути не поддерживаются, не даром Excel начинает ругаться, когда открываются две книги с разными именами из разных мест, так что используем какой-то другой метод, имя книги или, как я сделал тут, имя файла с книгой без пути. Именно из-за этого выскакивала Run-Time Error '9'.

ну... и c.Value тоже надо было не забыть