VBA solution NEEDED!!

[laceygirl]

ill try to show you what I needing.

column A / Column B / column c / column d / column e/ column F/
Current Employees / HIRE DATE/ TERM DATE / NO SHOW/ QUIT SAME DAY/ PREVIOUS EMPLOYEE/



when we hire someone I put their name in column a and then the hire date in b. when or if they quit get fired or don't show up etc, column c,d, and e will then have a date. At that point I want the name of that person to go from column a to column f automatically after I put the date into one of those columns. THANKS

 

[jimrward]

in F2 try
=IF(C2&D2&E2 <> "",A2,"")

 

[ArthriticPanda]

in F2 try
=IF(C2&D2&E2 <> "",A2,"")

I think the OP wants to move the person from one column to another Jim although your formula does the job well.

AP

 

[laceygirl]

yes thank you for the function it does well but yes I am trying to get the name from column a to column f. Thanks!

 

[laceygirl]

well I mean moved to column f and then deleted from column a.

 

[BiocideJ]

I believe this in the Worksheet object of the sheet with the data will do what you are looking for.
Note, if you make a mistake, and remove the date, the name will NOT move back to column A, however,
if you enter a date in column C and then a date in column D, the value in column F will not be overwritten with a blank.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("C:E")) Is Nothing Then  'the changed cell is in the date range to watch
        
        If IsDate(Target.Value) Then    'a date was just added in the watch range
            
            If Range("A" & Target.Row).Value <> "" Then 'only copy if there is a name in column A of the row changed
                
                Range("F" & Target.Row).Value = Range("A" & Target.Row).Value
                Range("A" & Target.Row).Value = ""
        
            End If
        End If
    End If
End Sub

 

[Rick Rothstein]

I believe this in the Worksheet object of the sheet with the data will do what you are looking for.
Note, if you make a mistake, and remove the date, the name will NOT move back to column A, however,
if you enter a date in column C and then a date in column D, the value in column F will not be overwritten with a blank.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("C:E")) Is Nothing Then  'the changed cell is in the date range to watch
        
        If IsDate(Target.Value) Then    'a date was just added in the watch range
            
            If Range("A" & Target.Row).Value <> "" Then 'only copy if there is a name in column A of the row changed
                
                Range("F" & Target.Row).Value = Range("A" & Target.Row).Value
                Range("A" & Target.Row).Value = ""
        
            End If
        End If
    End If
End Sub

You should really disable events when you have the Change event change values in cells so the event doesn't get raised again with each change you make in code. Here is how I would write it (note I do not check if the value entered in the Target cell was a date or not as those are date columns).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("C:E")) Is Nothing Then
    If Len(Cells(Target.Row, "A")) > 0 And Len(Target.Value) > 0 Then
      Application.EnableEvents = False
      Cells(Target.Row, "A").Cut Cells(Target.Row, "F")
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[laceygirl]

Please help I have tried to add this many times to my workbook but it isn't working with the steps I have found online. Is there anyway I can get a walk through on how to add and play this to my workbook.

 

[BiocideJ]

Please help I have tried to add this many times to my workbook but it isn't working with the steps I have found online. Is there anyway I can get a walk through on how to add and play this to my workbook.

I am reposting Rick's code because it is the most succinct and appropriately disables event handling

Open the Visual Basic Window (ALT+F11)
In the Project window on the left of the screen you should see a listing of all of the open workbooks and sheets in those workbooks.
Find the Worksheet that you want to have this run in (the one where you will be entering the dates) and double-click it.
This should change the main part of the screen to show the code that is entered for that object.

In that main area, paste the following code...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Target, Columns("C:E")) Is Nothing Then
    If Len(Cells(Target.Row, "A")) > 0 And Len(Target.Value) > 0 Then
      Application.EnableEvents = False
      Cells(Target.Row, "A").Cut Cells(Target.Row, "F")
      Application.EnableEvents = True
    End If
  End If
End Sub

Then when you make changes, it should do what you need.

 

[laceygirl]

Perfect thank you so much!