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]

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.

What column is this dropdown list in? Say something like column A or Column J
And is using Vba solution Ok.

 

[anhpp72]

What column is this dropdown list in? Say something like column A or Column J
And is using Vba solution Ok.

Hi the column is A

 

[My Aswer Is This]

I will get back with you in ten minutes or less
Do you want the row deleted from the original sheet after the move?
This will be done with vba

 

[My Aswer Is This]

Now I have another quesstion:
You said: 3 corresponding sheets

The you said:

3 items in my dropdown list: dead, clients, prospects and suspects
I see 3 here:
and capitalization is important

Is it exactly?
"dead"
"clients"
"prospects and suspects"

 

[anhpp72]

I will get back with you in ten minutes or less

Now I have another quesstion:
You said: 3 corresponding sheets

The you said:

3 items in my dropdown list: dead, clients, prospects and suspects
I see 3 here:
and capitalization is important

Is it exactly?
"dead"
"clients"
"prospects and suspects"

I would like to have them deleted once it is moved.
They are
"Dead"
"Clients"
"Prospects & Suspects"

 

[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 sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
The script runs when you enter a sheet name in column A
Put this script in just the sheet where you plan to enter the sheet name in column A

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2022  1:30:34 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
Dim Lastrow As Long
r = Target.Row
ans = Target.Value
Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(r).Copy Sheets(ans).Rows(Lastrow)
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]

Private Sub Worksheet_Change(ByVal Target As Range) 'Modified 6/20/2022 1:30:34 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 Dim Lastrow As Long r = Target.Row ans = Target.Value Lastrow = Sheets(ans).Cells(Rows.Count, "A").End(xlUp).Row + 1 Rows(r).Copy Sheets(ans).Rows(Lastrow) 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

I tried running the code but when I pressed run, it asked me to name the Macro. Any thoughts?

 

[My Aswer Is This]

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

I tried running the code but when I pressed run, it asked me to name the Macro. Any thoughts?

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

 

[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

It worked perfectly!! I can't thank you enough. I really appreciate it!!!