Solved: copy paste doesn't work

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

Code:

---

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.

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

that worked. thanks