select method

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?

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.


"R..VENKATARAMAN" wrote:

> 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?
>
>
>

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("A1:D20")

Rng.Interior.ColorIndex = 6


---
Regards,
Norman


"R..VENKATARAMAN" <venkat1926@touchtelindia.net> wrote in message
news:OV6FGJCPGHA.2176@TK2MSFTNGP10.phx.gbl...
> 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?
>

thank you for clarification. understood


"Norman Jones" <normanjones@whereforartthou.com> wrote in message
news:Oq609aCPGHA.1532@TK2MSFTNGP12.phx.gbl...
> 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("A1:D20")
>
> Rng.Interior.ColorIndex = 6
>
>
> ---
> Regards,
> Norman
>
>
> "R..VENKATARAMAN" <venkat1926@touchtelindia.net> wrote in message
> news:OV6FGJCPGHA.2176@TK2MSFTNGP10.phx.gbl...
>> 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?
>>
>
>

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


"R..VENKATARAMAN" <venkat1926@touchtelindia.net> wrote in message
news:OV6FGJCPGHA.2176@TK2MSFTNGP10.phx.gbl...
> 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?
>