Macros to triggger timestamp whenever one specific option is selected from the drop down list

[Marta1]

Hi All,

Hope you are doing great

I need your help to create a Macros to trigger timestamp whenever one specific option is selected from the drop down list

I have a file called Employee(Copy) where is already a event macros to copy and paste the values in the another file. So, I wanted to add addition features to this sheet without disturbing the existed code but I am bit lost.

Whenever the status is changed to "In progress" then macros should add the current time stamp in the column "In progress start" and once the "In progress" status gets changed to another status then macros should create a time stamp in the column "In progress end"

Could you please help me with this?

Please have a look at the attached photos

Thank you in Advance

Regards
Marta

 

[James006]

Modified Version 2

Private Sub Worksheet_Change(ByVal Target As Range)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Event macro to be placed in your Source Workbook            '
' As soon as User fills in the Status in Column G             '
' Added CHRONOLOGICAL In Progress 1 and in Progres 2          '
' and your two conditions are fulfilled , New record is copied'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Long, lastrow As Long, xcol As Integer
Dim wksh1 As Worksheet, wksh2 As Worksheet
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
i = Target.Row
Set wksh1 = ThisWorkbook.Sheets("Sheet1")
' Adjust to your specific situation for Destination Names :
' Workbook Name AND Worksheet Name
Set wksh2 = Workbooks("Employee(final).xlsx").Sheets("Sheet1")

lastrow = wksh2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    If Target.Offset(0, -4).Value >= 43586 Then
        If Target.Offset(0, -3).Value = "Charles" Or Target.Offset(0, -3).Value = "Nicolas" Then
            Select Case Target
                Case "In Progress"
                 ' Assumption : User will use the choice "In Progress" '''' Chronologically '''''
                 ' Use the First Empty Cell in Range I to L ( Columns 9-12 )
                 xcol = Range("I" & i & ":" & "L" & i).Find(What:="").Column
                 Target.Offset(0, xcol - 7) = Now
                Case "Completed"
                    ' All tests Passed - Copy Record and Add Flag next to copied record '''''''
                    Target.Offset(0, 1).Font.Name = "Wingdings"
                    Target.Offset(0, 1).Value = "ü"
                    wksh1.Range("A" & i & ":G" & i).Copy Destination:=wksh2.Range("A" & lastrow)
            End Select
        End If
    End If
End Sub

 

[James006]

Hi Marta,

Forgot to mention that, if you input data chronologically, you can have as many sub-levels (1,2,3,4,5,6,7, etc...) of your generic "In Progress" choice,
by simply extending the reference range beyond the L Column ... and adjust the macro accordingly ...

 

[Marta1]

Thank you so much for the time

It works

 

[James006]

Glad to hear it is all sorted out