VBA Code Newbie… Help!!

[Jenniferzp9]

I have an issue where I am trying to move data back and forth based on cell value. I currently have a drop down with the options PENDING, ACTIVE, & SETTLED. I have 2 sheets: Clients and Settled. Is there a way I can automatically move the data from “Clients” to “Settled” worksheet based on drop down selection? And also move it back from “Settled” and into “Clients” worksheet if need be?

 

[My Aswer Is This]

I have an issue where I am trying to move data back and forth based on cell value. I currently have a drop down with the options PENDING, ACTIVE, & SETTLED. I have 2 sheets: Clients and Settled. Is there a way I can automatically move the data from “Clients” to “Settled” worksheet based on drop down selection? And also move it back from “Settled” and into “Clients” worksheet if need be?

You said:
"I currently have a drop down with the options PENDING, ACTIVE, & SETTLED"
Where is this dropdown? we need to know what column
If the dropdown is in column A for example when you select the proper value that row will be copied to the other sheet

And when you say "Move" does that mean delete row from original sheet and copy to another sheet?
Now the second part would require another script in the other sheet also
Now we can have this done automatically but I suggest we use double click to make sure you do not accidently do this.
Double click on any cell in column A and that row will be copied to other sheet and deleted from original sheet

 

[Jenniferzp9]

You said:
"I currently have a drop down with the options PENDING, ACTIVE, & SETTLED"
Where is this dropdown? we need to know what column
If the dropdown is in column A for example when you select the proper value that row will be copied to the other sheet

And when you say "Move" does that mean delete row from original sheet and copy to another sheet?
Now the second part would require another script in the other sheet also
Now we can have this done automatically but I suggest we use double click to make sure you do not accidently do this.
Double click on any cell in column A and that row will be copied to other sheet and deleted from original sheet

Hi, yes I want to delete from original sheet and move to the “Settled” sheet. I have attached a photo of what I’m referring to. Hope it helps!

 

[My Aswer Is This]

Now I see your using a Mac with Mobile Web

Have you previously used Vba scripting and have it worked properly?

I'm writing scripts that work with Windows Excel 2013

 

[Jenniferzp9]

Now I see your using a Mac with Mobile Web

Have you previously used Vba scripting and have it worked properly?

I'm writing scripts that work with Windows Excel 2013

I am working on a windows with excel 2010. I am new to it. I experimented with it and was able to make it move to the other spreadsheet, but now it stopped working. I was never able to make it move back to the original sheet however.

 

[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

When you enter any sheet name in column B the script will copy that row to the sheet named by the value you entered into column B and delete that row from the sheet it was original in.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/14/2022  10:25:21 PM  EDT
If Target.Column = 2 Then
On Error GoTo M
Dim ans As String
Dim Lastrow As Long
Dim r As Long
ans = Target.Value
r = Target.Row
Lastrow = Sheets(ans).Cells(Rows.Count, "B").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