Drop down list selection causes row to be copied and pasted to another sheet

[karimarie]

I have an excel file that when I select "consultant" under "To be processed by" I want the rows values from Date to Status to be copied to another sheet. Sheet 1 called "Requests" and Sheet 2 is called "Consultant". I only need this to be for the consultant selection. I may be excel challenged so I need simple instructions. Please ask for clarification if needed[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Type[/TD]
[TD]Requested[/TD]
[TD]Dept.[/TD]
[TD]TH[/TD]
[TD]Locat[/TD]
[TD]Assign[/TD]
[TD]Status[/TD]
[TD]To Be Processed By[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]*I am drop down LIST with four options[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 9 Then Exit Sub
   If LCase(Target.Value) = "consultant" Then
      Range("A" & Target.Row).Resize(, 8).Copy Sheets("Consultant").Range("A" & Rows.Count).End(xlUp).Offset(1)
   End If
End Sub

To install this right click on the tab you want this to work on > view code > paste the code into the window that opens up.
Save the file as an xlsm file.
Then whenever you enter consultant into col I that row will be copied to a sheet called Consultant

 

[karimarie]

Thank YOU! I had been searching through codes all morning with no luck! This worked perfectly!

 

[Fluff]

Glad to help & thanks for the feedback