select method

[R..VENKATARAMAN]

windows(1).worksheets("s1").range("a1").select
workbooks("olddesign.xls").Worksheets("s1").range("a1").select
Neither of the above works one gives the error
"object does not support or method"
and other gives the error
"select method or range class failed"
I have to use
windows(1).activate
worksheets("s1").activate
range("a1").select


why? Where do I go wrong?

 

[Guest]

I've never gotten that to work either. I don't know the technical reason,
however, I will point out that VBA help example activates the worksheet and
selects a specific range using two separate statements (so I figure there
must be a reason).

I've found, however, that it is often not necessary to select/activate items
in order to work with them. For example:

workbooks("olddesign.xls").Worksheets("s1").range("a1").value = 5

should work just fine.

 

[Norman Jones]

Hi R,

You cannot make a selection on an inactive sheet.

By the way, in general, making selections is often unnecessary and
inefficient. It would usually be preferable to manipulate an object
variable, e.g,:

Dim SH As Worksheet
Dim Rng As Range

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1")
Set Rng = SH.Range("A120")

Rng.Interior.ColorIndex = 6

 

[R..VENKATARAMAN]

thank you for clarification. understood

Hi R,

You cannot make a selection on an inactive sheet.

By the way, in general, making selections is often unnecessary and
inefficient. It would usually be preferable to manipulate an object
variable, e.g,:

Dim SH As Worksheet
Dim Rng As Range

Set WB = ActiveWorkbook
Set SH = WB.Sheets("Sheet1")
Set Rng = SH.Range("A120")

Rng.Interior.ColorIndex = 6

 

[Chip Pearson]

You can't Select a cell on a sheet that is not active. However,
you can use Application.Goto to do the same thing.

Application.Goto
workbooks("olddesign.xls").Worksheets("s1").range("a1")
Selection.Value = 123

That said, it is almost never necessary to Select a cell, and
doing so is an expensive operation. Instead, just use the range
directly.

workbooks("olddesign.xls").Worksheets("s1").range("a1").Value =
123


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com