selecting a cell

M

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
 
M

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
 
B

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
 
R

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
 
Top