The problem is that
worksheets("sheet2").range
refers to Sheet2 but
cells(1,1),cells(1,20)
refers to the active sheet, and if Sheet2 is not the active sheet, you will
be attempting to set a range on Sheet2 to cells on the active sheet. Fully
qualified, your code is interpreted as:
set copy_rng =
Worksheets("Sheet2").Range(ActiveSheet.Cells(1,1), ActiveSheet(1,20))
You should write your code as
With Worksheets("Sheet2")
Set copy_rng = .Range(.Cells(1,1),.Cells(1,20))
End With
Note that there is a leading period before "Range", and the two "Cells".
This is required for the With statement.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
"carlo" wrote in message
...
Hi All
I tried following simple code:
'-----------------------------------------------------------
dim copy_rng as range
set copy_rng = worksheets("sheet2").range(cells(1,1),cells(1,20))
'-----------------------------------------------------------
It only seems to work, when I activate Sheet2! I can't remember this
ever happened. I am not trying to select anything, so why does it have
to be active?
Do I become senile? This thing is driving me crazy!
thanks for any help
Carlo