Try out the following code. If sheet2 is active, all 3 procedures work fine, but if sheet2 is not
active, TestRangeCells will not work but the other 2 procedures work fine. Anyone know why there is
a difference?

Sub TestRangeA1()
Sheets(2).Range("a1:a2") = 99
End Sub

Sub TestRangeCells()
Sheets(2).Range(Cells(1, 1), Cells(2, 1)) = 99
End Sub

Sub TestCells()
Sheets(2).Cells(1, 1) = 99
Sheets(2).Cells(2, 1) = 99
End Sub

When used without a qualifier, Cells function will refer to ActiveSheet

You should write:

With Sheets(2)
.range(.cells(1,1),.cells(2,1)) = 99
end with

HTH
--
AP

"Jeff" a écrit dans le message de news:
...
Try out the following code. If sheet2 is active, all 3 procedures work
fine, but if sheet2 is not active, TestRangeCells will not work but the
other 2 procedures work fine. Anyone know why there is a difference?

Sub TestRangeA1()
Sheets(2).Range("a1:a2") = 99
End Sub

Sub TestRangeCells()
Sheets(2).Range(Cells(1, 1), Cells(2, 1)) = 99
End Sub

Sub TestCells()
Sheets(2).Cells(1, 1) = 99
Sheets(2).Cells(2, 1) = 99
End Sub

aaaaaaahhhhh!!! Very nice! THANKS! I was thinking that cells was a property of the range object
and that you only had to qualify .range

"Ardus Petus" wrote in message
...
When used without a qualifier, Cells function will refer to ActiveSheet

You should write:

With Sheets(2)
.range(.cells(1,1),.cells(2,1)) = 99
end with

HTH