Excel Trigger on change help

kaanman 

Right-click the sheet tab of the sheet with the Y/N column.

Select 'View Code' from the context menu.

Copy the following code into it:

 

Private Sub Worksheet_Change(ByVal Target As Range)
    ' The other sheet where you want to store the row(s)
    Const strOther = "OtherSheet"
    ' The column with Y/N
    Const strYN = "M"
    ' The column for the time stamp
    Const strStamp = "T"
    ' Variables
    Dim wsh As Worksheet
    Dim r As Long
    Dim rng As Range
    ' Check whether the Y/N column has changed
    If Not Intersect(Columns(strYN), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        ' Find the last used row on the other sheet
        Set wsh = Worksheets(strOther)
        r = wsh.Range(strYN & wsh.Rows.Count).End(xlUp).Row
        ' Loop through the changed cells
        For Each rng In Intersect(Columns(strYN), Target)
            ' If the value has been changed to Y...
            If rng.Value = "Y" Then
                ' Then copy the data to the next available row
                r = r + 1
                rng.EntireRow.Copy Destination:=wsh.Range("A" & r)
                ' And set the time stamp
                wsh.Range(strStamp & r).Value = Now
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

 

Before testing it, change the constants at the beginning of the code according to your setup.

 

Make sure that you save the workbook as a macro-enabled workbook (.xlsm) and that you allow macros when you open it.