Problem setting values for a range

Hello,

I am trying to set some values for a range but am a bit stuck.
When using this code it works fine:

Workbooks("Book2.xlsx").Sheets("Sheet1").Range(Cells(1,1), Cells(1, 2)).Value = Workbooks("Book1.xlsx").Sheets("Sheet1").Range("A1:B1").Value

But when using this code i get a Run Time Error 1004

Workbooks("Book2.xlsx").Sheets("Sheet1").Range(Cells(1,1), Cells(1, 2)).Value = Workbooks("Book1.xlsx").Sheets("Sheet1").Range(Cells(1,1), Cells(1, 2)).Value

Can any body help me here and point out why this produces an error, i want to use the second option to set values for a variable range and prefer to use the Cells() method?

Thanks!

Could you provide your workbooks? Would be easier that way.
I think this might be caused because you need to clarify what workbook and cells you are refeering to, and the later part of your code is trying to reference the book2 cells.

You could try to add this bit of code at the start of the module:

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set wb1 = Workbooks("Book1.xlsx") 'first workbook
Set wb2 = Workbooks("Book2.xlsx") 'The other workbook

Set ws1 = wb1.Worksheets("Sheet1")
Set ws2 = wb2.Worksheets("Sheet1")

And replace that line of code with:

ws2.Range(ws2.Cells(1, 1), ws2.Cells(1, 2)).Value = ws1.Range(ws1.Cells(1, 1), ws1.Cells(1, 2)).Value

As you can see the difference is that my line is refering to a specific workbooks worksheets cells.

Thank you very much i think you helped me figure it out.

The issue was the way i selected my range, i was using

wb1.ws1.Range(Cells(x, y), Cells(x, y))

Whereas, the way your wrote it using works just fine

Range(ws1.Cells(x, y), ws1.Cells(x, y))

I tried it and all working! thanks again.