Cells method on another worksheet

[Giordano Bruno]

lngCol = 5, lngEndCol = 9

the line of code
Sheets("strCols").Range(Cells(2, lngCol), Cells(2, lngEndCol)).Copy
fails with a Run-time error 1003 Application-defined or object-defined error

the alternative
Sheets("strCols").Range("E2:I2").Copy
works fine.

If Sheet("strCols") is active at the time the first line of code is read, there is no problem, but I do not want to activate the sheet just for this line of code.

Does anyone have an idea what may be happening?

 

[AlphaFrog]

Try this...

Sheets("strCols").Range([COLOR="Red"]Sheets("strCols").[/COLOR]Cells(2, lngCol), [COLOR="Red"]Sheets("strCols").[/COLOR]Cells(2, lngEndCol)).Copy

Or this...

With Sheets("strCols")
.Range([COLOR="Red"].[/COLOR]Cells(2, lngCol), [COLOR="Red"].[/COLOR]Cells(2, lngEndCol)).Copy
End With

 

[Giordano Bruno]

Beautiful solution AlphaFrog.

I definitely prefer number two rather than using three references to the worksheet.

This seems to be a limitation of the cells method which is not shared by "Range".

Thanks again for leaping to my rescue.