Moving Data to Another Sheet Based on Dropdown Selection

[jeff88]

Hello,

I am trying to setup a workbook with two sheets, one for in-progress jobs and one for completed jobs. The in-progress jobs will have 1 row for each job and a column at the end will have a dropdown with a "Complete" option when the job is completed. When a user selects the "Complete" option, Excel will automatically move the row of data into the completed jobs sheet and delete the (now) empty row.

I have seen other threads on the web and this forum with this action (like this one), but I can't seem to get the code to work. I copy/pasted/changed sheets names & columns for what I need, but it doesn't seem to work. I've also tried working out my own code using bits from that link and some other Googled options without any luck.

I have a basic knowledge of code, but not enough to troubleshoot where the issue is.

I'm using Excel web app.

 

[mumps]

I think that I can see the problem. Change emd(xlup) to end(xlup). That was a typographical error on my part. Sorry for that.

 

[mumps]

You may have to restart Excel.

 

[jeff88]

If you are using this in SharePoint or similar macros won't work. You would need to open in excel and then activate the macro.

You could use a button on your sheet with this code. You will need to update sheet names etc to match your spreadsheet.To run the script you would need to open in Excel then press the button.

This code assumes both sheets have tables on them called Table2 and Table2.

t0ny84

How do I know if I am using it in SharePoint? I know we use SharePoint, but I'm opening Excel directly on desktop.
Also, do you have a code that will work automatically without the use of a button? I want to make this as foolproof as possible.

 

[jeff88]

I think that I can see the problem. Change emd(xlup) to end(xlup). That was a typographical error on my part. Sorry for that.

I changed it and restarted Excel, but still getting the same errors saying it's expecting semi-colons and commas. It's also saying it cannot find the "name" for a lot of lines.

 

[t0ny84]

Where is the Excel Spreadsheet saved? It should allow you to see.

Mumps code should work by updating emd to end.

Can you take a screenshot of the error.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 10 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Complete" Then
        With Sheets("Completed Jobs")
            Target.EntireRow.Copy .Cells(.Rows.Count, "A").end(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[jeff88]

Where is the Excel Spreadsheet saved? It should allow you to see.

Mumps code should work by updating emd to end.

Can you take a screenshot of the error.

It's saved to my OneDrive.

Here are two snips of the "output" errors and the "problems" errors. There are more if I scroll down the list of errors, but you can get the idea of what it's coming back with.

 

[t0ny84]

Opening the file on One Drive through the web would make the macros not work.

Regarding the errors can you put up a screenshot of your VBA Editor with the code.

Alt + F11 will open I the editor.

t0ny84

 

[jeff88]

Opening the file on One Drive through the web would make the macros not work.

Regarding the errors can you put up a screenshot of your VBA Editor with the code.

Alt + F11 will open I the editor.

t0ny84

It's saved to my OneDrive, but I'm opening it on the desktop. Maybe I can try saving my file to my documents folder? I attached the code exactly as I have it. Note the change from emd to end.

 

[t0ny84]

It shouldn't make a difference but yes please try saving a copy to your computer and seeing if the error still occurs.

 

[mumps]

The screen shots you posted don't look like the usual type of messages that Excel displays when a macro generates an error. Neither does the screen shot of your whole sheet. This what it should look like.

I duplicated the sample data you posted to look like this in Sheet1:

Book1
	A	B	C	D	E	F
1	Job #	Customer	Address	Permit	AHJ	Job Complete
2	123458	c	1236 Main	Issued	San Jose
3	123459	d	1237 Main	Issued	San Jose
4	123460	e	1238 Main	Issued	San Jose
5	123461	f	1239 Main	Issued	San Jose
6	123462	g	1240 Main	Issued	San Jose
7	123463	h	1241 Main	Issued	San Jose
Sheet1

This was the result of the macro in sheet Completed Jobs:

Book1
	A	B	C	D	E	F
1	Job #	Customer	Address	Permit	AHJ	Job Complete
2	123457	b	1235 Main	Issued	San Jose	Complete
Completed Jobs

You'll notice that this line of data has been deleted from Sheet1. Everything is working properly. What version of Excel are you using?