Auto copy and paste to another sheet / Insert Dates

[ClockingClock]

Hi, I have a sheet where several people manage for multiple projects.
In Sheet1, Column A has Project Names and Column B shows the status of each project.
Each project should have four stages; Not Started, In Progress, Follow up, and Completed, and I am trying to create a separate tab, Sheet2, that lists the Status and when it was changed.
For instance, if I change the status of Project A from "In Progress" to "Complete" in Sheet1, I want Sheet2 to show the date when Project A changed its status – an image is inserted below –.
In Sheet2, I am going to insert a formula to display project names, but it is very difficult for me to create a macro to record dates per status change while matching each project.
I would really appreciate it if you could please help me set up a macro to automatically copy and paste the status column (when there is a change) and insert date.

 

[My Aswer Is This]

Your image is hard for me to read as the background is black as is the text
So from what i understand if you change the value in column B of Master Sheet you want what copied to sheet named Status.
And just copy this to next empty cell in column A of sheet named Status.

If this is not correct please explain more

 

[Peter_SSs]

In Sheet2, I am going to insert a formula to display project names,

You can do that if you want, but this code will add the project name to Sheet2 if it does not already exist there.

Try this Worksheet_Change event procedure with a copy of your workbook.
To implement ..
1. Right click the Sheet1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Test.

I have assumed that column B of Sheet1 has Data Validation so that any entry will be one of those 4 status values that you mentioned.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, rFound As Range
  Dim oSet As Long
 
  Set Changed = Intersect(Target, Range("B2:B" & Rows.Count))
  If Not Changed Is Nothing Then
    With Sheets("Sheet2")
      For Each c In Changed
        If c.Value <> "" Then
          oSet = Application.Match(c.Value, Array("Not Started", "In Progress", "Follow Up", "Completed"), 0)
          Set rFound = .Columns("A").Find(What:=c.Offset(, -1).Value, LookAt:=xlWhole)
          If rFound Is Nothing Then
            Set rFound = .Range("A" & Rows.Count).End(xlUp).Offset(1)
            rFound.Value = c.Offset(, -1)
          End If
          rFound.Offset(, oSet).Value = Date
        End If
      Next c
    End With
  End If
End Sub

 

[My Aswer Is This]

Glad to see Peter has now provided a script. I will move on to help with others here looking for help.