selecting a cell

[MJKelly]

Hi,
using excel 2003.

Can you tell me why the following code is not working? Why do I have
to perform this action in two steps?

Not working:
ThisWorkbook.Sheets("Staff Monday").Range("B2").Select

Working:
ThisWorkbook.Sheets("Staff Monday").Select
Range("B2").Select

I have pasted values to a large range in "Staff Monday" and do not
want the range to remain selected once the code has run.

thanks,
Matt

 

[Mike H]

Hi,

In the first snippet of code your trying to select a range on an inactive
worksheet and you can't do that or at least I don't know how.

On the second you make the sheet active so can select a range.

Mike

 

[Bernie Deitrick]

Matt,

You cannot change the selection on an inactive sheet.

Note that you do not need to select a range to paste values - this works just as well, with no
selection:

ThisWorkbook.Sheets("Staff Monday").Range("G1:G10").Value =
ThisWorkbook.Sheets("DataBase").Range("B1:B10").Value

HTH,
Bernie
MS Excel MVP

 

[Roger Govier]

Hi Matt

You can't select a cell on an inactive sheet. If your Sheets("Staff Monday")
is the active sheet at the time, then the command would work, as would the
more simple
Range("B2").Activate

I suspect you are copying by selecting the range first.
There is no need to do this e.g.

Sheets("Sheet1").Range("A2:A20").Select
Selection.Copy Sheets("Sheet3").Range("A2")
will make the ranges A2:A20 be marked on both sheets and Sheet3 will be the
active sheet at that point

whereas
Sheets("Sheet1").Range("A2:A20").Copy Sheets("Sheet3").Range("A2")
will make the copy and whatever sheet you were on at the time the command
was executed will remain the active sheet.
Only the range A2:A20 will be marked on Sheet3