Copy paste values triggered by worksheet change event is not working

Question

I am using worksheet change event to trigger copy paste values. Worksheet change code is in the sheet2

Sub worksheet_change(ByVal Target As Range)
Application.EnableEvents = True
Set Target = Range("AB2")
If Target.Value = "OK" Then
    Call myTR1
End If

Please note AB2 cell takes it's value from another sheet

Copy paste code is in a Module

Sub myTR1()
Sheets("BA1").Range("AR6:AS8").Value = Sheets("BA1").Range("AL17:AM19").Value
End Sub

When target range changes to "OK", my copy paste macro is not triggering. What am I doing wrong?

Your worksheet_change has small w and small c so I am guessing the code is in a module and not the relevant worksheet — Siddharth Rout
Try to use other name for variable than Target, for example TargetCell. Isn't it 'special' word in VBA? — Teamothy
@SJR yes, worksheet change event code appears. Copy and paste values code is in one of the Modules. — Will
Please note AB2 cell takes it's value from another sheet OH DAMN!!!! I just noticed... In this case Worksheet_Change will not work — Siddharth Rout
You may want to rethink this from Worksheet_Calculate() point of view — Siddharth Rout

SpyingSphinx SpyingSphinx · Accepted Answer · 2019-10-12T11:57:49

Using your eaxct code worked, although you didnt have end sub in your example?

EDIT: Bear in mind the 'OK' is case sensitive so it will have to be in uppercase to fire, if you want it to fire either on lower or upper you can use the second code.

    Sub worksheet_change(ByVal Target As Range)
    Application.EnableEvents = True
    Set Target = Range("AB2")
    If Target.Value = "OK" Then
        Call myTR1
    End If
    End Sub

    Sub worksheet_change(ByVal Target As Range)
    Application.EnableEvents = True
    Set Target = Range("AB2")
    If Target.Value = "OK" Or Target.Value = "ok" Then
        Call myTR1
    End If
    End Sub

Copy paste values triggered by worksheet change event is not working

1 Answers