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

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

"MJKelly" wrote:


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

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").Va lue

HTH,
Bernie
MS Excel MVP


"MJKelly" wrote in message
...

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

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
--
Regards
Roger Govier

"MJKelly" wrote in message
...

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