Drop Box Macro Trigger

[roketmanp]

I have a workbook that has a running master list of job candidates. In the master list there are columns such as candidate, recruiter, location, and other general information. One of the columns contains status information such as "offer extended", "offer accepted", "reneged", "declined", etc that can be selected from a drop down box.

There are other tabs in this workbook with titles "Hired", "Declined", and other titles corresponding to the selections in the drop box above (with identical columns).

What I would like to do is have a macro put in place that, for example, when "Offer Accepted" is chosen from the drop down box in the master list, would automatically take all that specific candidates information (in the row) and place it in the "Hired" workbook and remove the information from the master list.

There will only be certain selections from the Drop-down box that will trigger a cut and paste into lists on other tabs as well.

Master List Example:

Candidate	Recruiter	Location	Status (Choose from Drop-Box)	Start Date
Jim	Steve	NY	Offer Accepted	1/1/17
Karen	John	NY	Offer Extended	1/3/17
Bill	Steve	SF	Reneged
Tanya	Steve	LA	Declined

<tbody>
</tbody>

I am new to VBA but willing to follow VBA instructions if needed.

 

[My Aswer Is This]

So the only thing you are asking for is:
Your quote:

"Offer Accepted" is chosen from the drop down box in the master list, would automatically take all that specific candidates information (in the row) and place it in the "Hired" workbook and remove the information from the master list.

And you do mean "Hired" "Workbook" not Hired Worksheet

And what happens if they select some other value from the drop down list?

Or do you mean what ever selection they make from the drop down list that row will be copied to that sheet. For example if they choose Declined from the dropdown that row will be copied to sheet named "Declined"

 

[roketmanp]

I apologize I meant "Hired" Worksheet.

I was using the "Offer Accepted" as an example of what I would like done. Ideally, along with this example, I would want the candidates information to be cut and paste into the "reneged" worksheet if "reneged" is selected in the drop box, the "Declined" worksheet if "Declined" is accepted, and so on. However, if "Offer Extended" is selected, that would be an example of a status that I would want not to trigger anything (no cut and paste, all information remains static). There will be a few selections that would fall into this circumstance as well.

Here is the list of the choices from the drop box. I have made bold the selections that I would want to trigger a cut and paste to different worksheets. The non-bold selections would not trigger anything.

Selections:
Offer Extended
Offer Accepted
On-Site interviews scheduled
Phone Interviews scheduled
Hold
Negotiating Offer
Declined
Passed
Reneged

 

[My Aswer Is This]

And can I properly assume the drop down list is in column "D"?

 

[roketmanp]

I have simplified the "Master List" worksheet to 5 columns for the sake of the post. The actual worksheets contain 10 columns of information per candidate (including candidate name). The drop down list is in column "G" actually.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the Master sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Be sure you have your sheets made and named exactly as shown in this script

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim ans As String
ans = Target.Value
If ans = "Offer Accepted" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
If ans = "Declined" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
If ans = "Passed" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
If ans = "Reneged" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
End If
End Sub

 

[roketmanp]

This works perfectly. Thank you so much!

 

[roketmanp]

Is there any way the date that the selection was made could be inserted into column J on each of the sorted workbooks?

Thanks again for the help.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

This works perfectly. Thank you so much!

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'And Date
If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Dim ans As String
ans = Target.Value
If ans = "Offer Accepted" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Cells(Target.Row, "J").Value = Date
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
If ans = "Declined" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Cells(Target.Row, "J").Value = Date
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
If ans = "Passed" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Cells(Target.Row, "J").Value = Date
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
If ans = "Reneged" Then
Lastrow = Sheets(ans).Cells(Rows.Count, "G").End(xlUp).Row + 1
Cells(Target.Row, "J").Value = Date
Rows((Target.Row)).Copy Destination:=Sheets(ans).Rows(Lastrow): Rows(Target.Row).Delete
End If
End If
End Sub