Since you are working between sheets, you will have to qualify your range
object statements by specifying the sheet for each object.

Sub cpynpst()
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")
With wks1
If .Range("A1") = .Range("B2") Then
.Range("D1").Copy .Range("E1") ' <<<copies in same WS
End If
End With
If wks1.Range("A1") = wks1.Range("B2") Then
wks1.Range("D1").Copy wks2.Range("E1") '<< cpy to 2nd WS
End If
End Sub

When working between sheets, it is a good practice to qualify all of your
range objects (cell references and ranges) with their applicable worksheets.
That way VBA won't have to guess which one you really want to address.


"Marie" wrote:

Ok...... basically, I assume you answered my question..... problem is, I
don't know what to do with the answer. Would you please give 'idiot' proof
directions on how to proceed? Do I copy/paste everything from 'private
sub..........to end sub' into a cell and adjust it for my ranges? Then copy
it to each cell I need it to put the answer in? Do I put it somewhere in the
worksheet to execute under a macro key? I'm sorta lost here. I know just
enough to be dangerous. Please help out.

"GTVT06" wrote:

This worked for me... I just tested it on row 1, but it can be
adjusted for your needs

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = Range("B1").Value Then Range("D1").Copy
Range("E1")

End Sub