Thread: using cells to define range gives run time error

I run a macro with the following code:

Code:

    Dim myArray() As Variant
    myArray = Worksheets("name1").Range("A1:B1").Value
    Worksheets("name2").Range("A1:B1").Value = myArray

And it works fine, copying the cells from name1 to name2.

If I replace the code with this:

Code:

    Dim myarray() As Variant
    myarray = Worksheets("name1").Range(Cells(1, 1), Cells(1, 2)).Value
    Worksheets("name2").Range(Cells(1, 1), Cells(1, 2)).Value = myarray

I get Run time error 1004: Application defined or object defined error

Why?

Many thanks!

Any unqualified range reference in a code module refers to the active sheet:

Code:

    myarray = Worksheets("name1").Range(Cells(1, 1), Cells(1, 2)).Value
    Worksheets("name2").Range(Cells(1, 1), Cells(1, 2)).Value = myarray

Since only one (at most) of those sheets can be active at a time, one of those lines will ALWAYS generate an error.

You can qualify the cells inside the two argument form of Range

Code:

myarray = Range(Sheets("Name1").Cells(1,1), Sheet("Name1").Cells(1,2)).Value

With Sheets("name2")
    Range(.Cells(1,1), .Cells(1,2)).Value = myarray
End With