Solved: copy paste doesn't work

[kavkazi]

Hi

I'm trying to do a copy and paste between two different excel files.

I am able to change cells values in the two different files successfuly. But a copy paste between them doesn't work for some reason.

Here is the code in test1.xls. There is another file in the desktop called test2.xls



Sub Update()

Dim XLApp As Object
Set XLApp = New Excel.Application

XLApp.Visible = True
XLApp.Interactive = True

XLApp.Application.Workbooks.Open ("C:\Users\vdaga7c\Desktop\test2.xls")

'changes value in test2 file successfully
XLApp.Application.Sheets("sheet1").Cells(1, "A").Value = "Hello in second file"

'changes value in test1 file successfully
Sheets("sheet1").Cells(1, "A").Value = "Hello in first file"

'doesn't work
XLApp.Application.Sheets("sheet1").Range("A1").copy Destination:= Sheets("sheet1").Range("B2")

XLApp.Application.ActiveWorkbook.Close SaveChanges:=False
XLApp.Quit

End Sub


I get the following error:

Run time error 1004
Copy method of range class failed.

[shrivallabha]

Just curious. Why do you want to create another instance of Excel? You can certainly use:
Application.Workbooks.Open ("C:\Users\vdaga7c\Desktop\test2.xls")
And then explicit workbooks names before like:
Workbooks("test2").Sheets("sheet1").Range("A1").copy Destination:= Workbooks("test1").Sheets("sheet1").Range("B2")

[kavkazi]

that worked. thanks