Sheet Select

[Richard]

Hi

Why does this work from any sheet:
ActiveWorkbook.Sheets("EOM Report").Select
ActiveWorkbook.Sheets("EOM
Report").Range("A1").Offset.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.Offset.End(xlUp).Offset(1, 1)).Name =
"ARRAYAREA"

While this doesn't:
ActiveWorkbook.Sheets("EOM
Report").Range("A1").Offset.End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.Offset.End(xlUp).Offset(1, 1)).Name =
"ARRAYAREA"


Unfortunately I have general problems with having to select a sheet first to
then work on the ranges / data within that sheet.

Please help

Thanks

 

[Bob Phillips]

Because you can only select a range within the active sheet as it requires a
screen refresh, and selecting a range doesn't automatically select the sheet
first.

But you can do it without selecting

Dim rng As Range

Set rng = ActiveWorkbook.Sheets("EOM
Report").Range("A1").Offset.End(xlDown).Offset(0, 1)
Range(rng, rng.Offset.End(xlUp).Offset(1, 1)).Name = "ARRAYAREA"

and the first Offset is also redundant

Dim rng As Range

Set rng = ActiveWorkbook.Sheets("EOM
Report").Range("A1").End(xlDown).Offset(0, 1)
Range(rng, rng.Offset.End(xlUp).Offset(1, 1)).Name = "ARRAYAREA"


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)