View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
Chip Pearson Chip Pearson is offline
external usenet poster
 
Posts: 7,247
Default Set Range.....am I to blind?

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