Worksheet Change Event - copy cell to another sheet

[dhstein]

I'm coding a Worksheet Change event to trigger when a cell changes. It works
well so far. The problem is I want to copy a cell from this worksheet to
another worksheet. When I specify the other worksheet, the cell gets copied
wherever the cursor was positioned in the target worksheet - not in the cell
I want. here is the copy code:

Range("BC3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Test1").Select
Range("Test1!B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


The worksheet Change event is in the worksheet code - so this may be the
problem. Is it possible to do this? Thanks for any help on this.

 

[Gary''s Student]

Sheets("Test1").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Dave Peterson]

When the code is in a worksheet module, the unqualified ranges belong to the
sheet with the code. In a general module, the unqualified range belongs to the
activesheet.

So you could qualify the ranges:

Range("BC3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Test1").Select
Sheets("Test1").Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

But even better is to drop all those .select's and just assign the value:

worksheets("test1").range("B3").value = me.range("bc3").value

If you really wanted, you could still do the copy|paste special|values, too:

me.range("Bc3").copy
worksheets("test1").range("b3").pastespecial paste:=xlpastevalues