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]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your in-progress jobs sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Change the column number (in red) to the column number of the column that contains the drop down. The macro assumes that you have a sheet named "Completed Jobs". Close the code window to return to your sheet. Select "Complete" in your drop down.

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").emd(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[jeff88]

Hi mumps, thanks so much for replying. Here is a snip of the right click menu on the sheet name. It doesn't seem I have a "View Code" option. Is this a limitation in the web app? My desktop Excel has the option in the right click menu.

 

[mumps]

I’m assuming you are using the PC version. It could be that the web app doesn’t allow macros. I’m not familiar with that version.

 

[jeff88]

I'm using the desktop app now. Hopefully I can upload it to the web and the script will still work.

This is the error I was originally getting when trying it myself. I'm not sure where to be putting the semi-colons and commas within the line #.

 

[mumps]

Are you adding lines of code to the macro i suggested?

 

[jeff88]

Are you adding lines of code to the macro i suggested?

None. Copy your code, delete the code that it auto-populates, paste your code, change the sheet name details for my worksheet, tab-bed lines over to match yours because it pastes with a left justification.

 

[mumps]

I’m sorry but I don’t follow what you are doing. All you have to do is follow the instructions in Post #2. If that doesn’t work, use the XL2BB addin (icon in the menu) to post a screen shot (not a picture) of your sheet. Alternatively, you could upload a copy of your file to DropBox and post a link to your file.

 

[jeff88]

Company policy doesn't allow me to download outside programs. I attached a pic of the whole sheet. You can see the errors in the bottom corner that Excel is giving me.

 

[t0ny84]

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.

Sub CopyRowIfComplete()
    Dim table1 As ListObject
    Dim table2 As ListObject
    Dim lastRowTable1 As Long
    Dim lastRowTable2 As Long
    Dim i As Long
   
    ' Set the tables
    Set table1 = Application.range("Table1").ListObject
    Set table2 = Application.range("Table2").ListObject
   
    ' Find the last row in Table1
    lastRowTable1 = table1.ListRows.Count
   
    ' Loop through Table1
    For i = 1 To lastRowTable1
        ' Check if the value in column F is "complete"
        If table1.ListRows(i).Range(, "F").Value = "complete" Then
            ' Find the last row in Table2
            lastRowTable2 = table2.ListRows.Count
           
            ' Copy the entire row from Table1 to the bottom of Table2
            table1.ListRows(i).Range.Copy Destination:=table2.ListRows(lastRowTable2 + 1).Range
     
  End If
    Next i
End Sub

t0ny84