Excel Trigger on change help

[kaanman]

I have a lot to learn with Excel, so here's what I need if anyone can help me.

For work, I upkeep a excel spreadsheet for project status, but also have a history with finished projects. 

I basically want to trigger a change in a cell on one sheet, from N to Y (for example), and it will take the information on that row, and store it to another sheet at the top. 

Bonus pts: I need also to have a cell on the second sheet that will fill the timestamp of when I did the trigger.

Any resources or help?

[#2]

@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.

[#3]

@Hans Vogelaar 

That's working really well. 

Just one edit I could use help with. On the sheet that the information is being saved too, can the script create a new row at 2 (row 1 is a header row) and then just copy the data? There for the latest information is always at the top?

[#4 · Accepted solution]

@kaanman 

Here is a new version:

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 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)
        ' 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
                rng.EntireRow.Copy
                wsh.Range("A2").EntireRow.Insert
                ' And set the time stamp
                wsh.Range(strStamp & 2).Value = Now
            End If
        Next rng
        Application.CutCopyMode = False
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

[#5]

@Hans Vogelaar 

Perfect. Thank you!!