VBA code to moving entire row based on value from drop down list

[anhpp72]

Hi all. I was hoping to get your help on this. I have 3 items in my dropdown list: dead, clients, prospects and suspects and 3 corresponding sheets. I want VBA to automatically move the entire rows to the corresponding sheet based on the value selected. I am really new on this so any help will be much appreciated.

 

[My Aswer Is This]

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

 

[anhpp72]

You can always enter any sheet name you want just make sure that sheet is in your workbook or you will get an error message

This type script does not run when you press a button.
This script runs when you enter a sheet name in column A of the sheet you enter the sheet name in column A.

See in my instruction above I said:
The script runs when you enter a sheet name in column A

Do I have to do this whole process every time I open the file?

 

[My Aswer Is This]

Do I have to do this whole process every time I open the file?

No. The script will stay where you put it and you can use it anytime.
But it will only work in this workbook.

If you want it another workbook you will have to put it in that workbook also.
And it only works in the worksheet you put it in.

 

[My Aswer Is This]

Try entering in a sheet name that does not exist and see what happens.
You will get a popup message

 

[anhpp72]

Try entering in a sheet name that does not exist and see what happens.
You will get a popup message

Just tried and yep, you are absolutely right! Is there any way the row can be to the top (row 2)?

 

[My Aswer Is This]

Just tried and yep, you are absolutely right! Is there any way the row can be to the top (row 2)?

It is true the first time you run the script for a particular sheet it is copied to row 2
Is that a problem?

Or are you saying every time you copy a row to another sheet you want it copied to row 2

 

[anhpp72]

I want it copied to row 2. So essentially new entry goes on top instead of bottom.

 

[My Aswer Is This]

I want it copied to row 2. So essentially new entry goes on top instead of bottom.

I will see and get back with you.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2022  2:23:06 AM  EDT
If Target.Column = 1 Then
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim ans As String
r = Target.Row
ans = Target.Value
Sheets(ans).Rows(1).Offset(1).Insert
Rows(r).Copy Sheets(ans).Rows(2)
Rows(r).Delete
End If
Exit Sub
M:
MsgBox "We had a problem " & vbNewLine & "You entered " & Target.Value & vbNewLine & "There is no sheet by that name"
End Sub

 

[anhpp72]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2022  2:23:06 AM  EDT
If Target.Column = 1 Then
On Error GoTo M
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim ans As String
r = Target.Row
ans = Target.Value
Sheets(ans).Rows(1).Offset(1).Insert
Rows(r).Copy Sheets(ans).Rows(2)
Rows(r).Delete
End If
Exit Sub
M:
MsgBox "We had a problem " & vbNewLine & "You entered " & Target.Value & vbNewLine & "There is no sheet by that name"
End Sub

That is amazing. It worked perfectly. Thanks again!!!