In a worksheet module, the unqualified use of the term RANGE refers to the
sheet containing the code, so in the Sheet2 code module

Dim ws as Worksheet
set ws = Worksheets("Sheet1")
Set AllCells = Range(ws.Cells(1,1), _
ws.Cells(rows.count,1).End(xlup))

is equivalent to
Dim ws as Worksheet
set ws = Worksheets("Sheet1")
Set AllCells = Worksheets("Sheet2").Range(ws.Cells(1,1), _
ws.Cells(rows.count,1).End(xlup))

Which causes an error since the ranges are on different sheets.

In a general module, the unqualifed Range doesn't appear to have this
restricition.

--
Regards,
Tom Ogilvy


"Paul" <PaulNieboer HEREGOESTHEAD Gmail.com wrote in message
...

Set AllCells = worksheets(1).Range(Worksheets(1).Cells(1, 1), _
Worksheets(1).Cells(65536,1).End(xlUp))

or more concise

with Worksheets(1)
Set AllCells = .Range(.Cells(1, 1), _
.Cells(65536,1).End(xlUp))
End With

--
Regards,
Tom Ogilvy


Cheers Tom,

I would never had thought about putting worksheets in front of, and right
after RANGE. I have been looking quite some time in this newgroups and
others for this answer!!

Now it works like a charm, thanks!!! (btw I now use the With...End With
for
obvious reasons)