copy paste using Range(Cells,Cells) - error 1004

[DonAndress]

Hello.

I have two lines of code.
Can anyone please help me understand why first one works and the second one does not?

Workbooks(Wkb).Sheets(Sht).Range("C12:BF12").Value = Workbooks(Wkb).Sheets(ShtS).Range("C12:BF12").Offset(i, 0).Value

Workbooks(Wkb).Sheets(Sht).Range(Cells(12, 3), Cells(12, allCols)).Value = Workbooks(Wkb).Sheets(ShtS).Range(Cells(12 + i, 3), Cells(12 + i, allCols)).Value

All variables have values, both lines refer to the same Range (I checked in the Immediate window).
allCols = 58 = BF
The second line is throwing me "Run-time error '1004', Application-defined or object-defined error".
How to fix that to properly use Cells?

 

[RoryA]

This is a very common question: you need to qualify the Range and Cells properties with the same worksheets:

Workbooks(Wkb).Sheets(Sht).Range(Workbooks(Wkb).Sheets(Sht).Cells(12, 3), Workbooks(Wkb).Sheets(Sht).Cells(12, allCols)).Value = Workbooks(Wkb).Sheets(ShtS).Range(Workbooks(Wkb).Sheets(ShtS).Cells(12 + i, 3), Workbooks(Wkb).Sheets(ShtS).Cells(12 + i, allCols)).Value

Using Worksheet variables would make the code neater and easier to maintain.

 

[Alex Blakenburg]

I would as Rory suggested be using variables for the sheets but I believe you can cut it down to something like this:

    With Workbooks(Wkb).Sheets(Sht)
        Range(.Cells(12, 3), .Cells(12, allCols)).Value = Range(Workbooks(Wkb).Sheets(ShtS).Cells(12 + i, 3), Workbooks(Wkb).Sheets(ShtS).Cells(12 + i, allCols)).Value
    End With

 

[RoryA]

I would not recommend using unqualified Range calls. If the code is in a worksheet module, it will cause an error.

 

[DonAndress]

Rory, Alex.

Thank you so much, it works now.
I also changed the code to use Worksheet variables.

Is it always mandatory to qualify Cells?

 

[RoryA]

Not mandatory (any more than it is with Range), but it's always safer.