Drop Down Selections

[jmk15315]

I have a simple spreadsheet that has multiple columns of information with each row as a new "task". One of the columns has a dropdown list for the status of the task (N/A, Not Started, In Progress, Completed & Filed).

Is there a way to have the sheet automatically copy that specific row of sheet1 to the last row of sheet2, then delete the row from sheet1 that was just copied/pasted? I am trying to avoid adding checkboxes or buttons. Hoping there is a quick macro that can execute based on the selection of "Filed" in that field.

Any thoughts?

 

[mumps]

Do you want to copy the row automatically when you make a selection in the drop down list?

 

[jmk15315]

Correct, Once "Filed" is selected, I would like it to copy/paste/delete

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Change the number "1" (in red) to the column number where you have your drop down list. Close the code window to return to your sheet. Select "Filed" in any row.

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

 

[jmk15315]

The status "FILED" is in column "K" (11) so I changed the second line of code
From"If Target.Column <> 1 Then Exit Sub"
To "If Target.Column <> 11 Then Exit Sub"

I get no action when changing the value in the column to "Filed". I thought perhaps it might be case sensitive, so I changed
From If Target = "Filed" Then
to If Target = "FILED" Then
but that did not return any different results.

I have ensured that the two sheets being used for the transfer of data are configured the same (cell width) and still no change.

Thoughts?

 

[mumps]

I tested the macro on some dummy data and it worked properly. Did you place the macro in the worksheet code module as I described in Post #4? Also, if you tried the macro and it produced an error at any time, try running this short macro first:

Sub test()
    Application.EnableEvents = True
End Sub

and then try the Worksheet_Change macro again. If it still doesn't work, it would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[jmk15315]

I did, yes. In reading the code to try and understand (learn) what is taking place, I see the following:

    If Target = "Filed" Then
        With Target.EntireRow
            .Copy Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1)
            .Delete
        End With
    End If

The intent is to "Copy" entire row from Sheet1 ("CDRH Tracking Report") that has a value of "Filed" in Column "K" and paste to Sheet2 ("Reports Filed") to the last row. Then delete the entire row from Sheet1 ("CDRH Tracking Report")

I see the "Copy" command, and I can change the name of the sheet but I don't see to where it is pasting this data. It is also not deleting the row from the "Source" sheet.

I apologize for being dense, I am trying to learn but I am definitely a beginner for sure. What am I missing?

 

[mumps]

This is the macro with explanatory comments:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 11 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Filed" Then
        With Target.EntireRow 'references the entire row
            .Copy Sheets("CDRH Tracking Report").Cells(Sheets("CDRH Tracking Report").Rows.Count, "A").End(xlUp).Offset(1) 'copies the referenced row to first blank row in "CDRH Tracking Report"
            .Delete 'deletes the referenced row
        End With
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

As I mentioned, the macro worked properly for me in a dummy file. Please follow the instructions in Post #6 to attach screenshots or upload your file.