Cut Rows and paste in new sheet when cell meets certain condition

[griggsa14]

Hello,

I was wondering how to right a code to cut an entire row and paste in another sheet when one cell meets a specific criteria. I have a checklist in column A starting in A2 that has the options -, Pending, Completed, Denied, Mistake-Abandoned. I also have 5 tabs labeled Overview, Pending, Completed, Denied, Mistake-Abandoned going left to right. The dropdown in column A is in every cell individually from A2 all the way to the end of the sheet. I would like it so that when someone switches the dropdown selection to completed in any of the A column that it automatically cuts the whole row from the pending sheet and pastes into the Completed sheet. The headers are the same in both sheets across the top row from A-K. I would also like it so that when it pastes into the new worksheet it goes to the next available open row so that it does not delete any old information. Ideally this would all happen automatically as well. Any help I get would be greatly appreciated and if you need more information let me know.

Thank you

 

[JoeMo]

Welcome to the Forum!

This sheet code goes in the sheet that holds the options dropdowns.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, i As Long
Set R = Intersect(Range("A:A"), Target)
If Not R Is Nothing Then
    For i = R.Count To 1 Step -1
        If R(i).Value = "Completed" Then
            Application.EnableEvents = False
            With R(i).EntireRow
                .Copy Destination:=Sheets("Completed").Cells(Sheets("Completed").Rows.Count, _
                        "A").End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    Next i
End If
Application.EnableEvents = True
End Sub

Whenever a change of col A cells to "Completed" is made by a user, the completed row will be copied to the Completed worksheet.

 

[griggsa14]

Thank you so much for the code. I am having one issue that once I inserted into the coding for that specific sheet and I try to change the drop down I am now getting the error <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85)}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85); min-height: 12.0px}</style>Compile error:


Ambiguous name detected: Worksheet_Change

Any idea why this may be. Macros are enabled.

 

[JoeMo]

Thank you so much for the code. I am having one issue that once I inserted into the coding for that specific sheet and I try to change the drop down I am now getting the error <style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85)}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; color: #000000 ; color: rgba(0, 0, 0, 0.85); min-height: 12.0px}</style>Compile error:


Ambiguous name detected: Worksheet_Change

Any idea why this may be. Macros are enabled.

Perhaps you have another worksheet_change event macro in the same sheet? You can only have one. If you have more than one, post all and someone can help you consolidate them to one macro.

 

[griggsa14]

I posted below all the coding I have for the specific sheet of pending which is sheet 5. The two are separated but the top one was to allow a multiselection dropdown lis in column H.

Private Sub Worksheet_Change(ByVal Target As Range)


'Code by Sumit Bansal from https://trumpexcel.com
' To make mutliple selections in a Drop Down List in Excel


Dim Oldvalue As String
Dim Newvalue As String


On Error GoTo Exitsub
If Target.Address = "$H$2" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, i As Long
Set R = Intersect(Range("A:A"), Target)
If Not R Is Nothing Then
For i = R.Count To 1 Step -1
If R(i).Value = "Completed" Then
Application.EnableEvents = False
With R(i).EntireRow
.Copy Destination:=Sheets("Completed").Cells(Sheets("Completed").Rows.Count, _
"A").End(xlUp).Offset(1, 0)
.Delete
End With
End If
Next i
End If
Application.EnableEvents = True
End Sub

 

[JoeMo]

I thought the dropdowns were in col A? The dropdown code seems to be localized to cell H2. You can combine the two codes by placing the first code below the one I posted under a single "Private Sub Worksheet_Change(ByVal Target As Range)" opening line and with a single "End Sub" line.

 

[griggsa14]

Ok thank you. I have two drop downs. The drop down in A is a single selection to determine status which would determine the cut and paste into a new sheet. The second drop down isn't related to that it is a multi selection drop down that I had to code that way so I could select multiple options in a drop down for that one cell and I just put that all the way down column H, but what happens in H shouldn't effect anything with the other code.

 

[JoeMo]

Ok, that's correct, but you must merge the two in the order I suggested. Otherwise, your multi-selection dropdown code will cause an Exit Sub before the code I posted is uncovered, whenever changes are made in col A.

 

[griggsa14]

Ok I merged them and everything else is working except now my multiselection dropdown in H is only working in H2. I had previously did a data validation in that cell for my list then wrote the code and once I selected all the rest of the cells it asked to copy the validation to all and I said yes and they would all allow the same multiple selections. Now only H2 is allowing the multiple selections. Is there something new I need to change the other code to so that it goes all the way down column H excluding H1.

 

[JoeMo]

Ok I merged them and everything else is working except now my multiselection dropdown in H is only working in H2. I had previously did a data validation in that cell for my list then wrote the code and once I selected all the rest of the cells it asked to copy the validation to all and I said yes and they would all allow the same multiple selections. Now only H2 is allowing the multiple selections. Is there something new I need to change the other code to so that it goes all the way down column H excluding H1.

Has nothing to do with the code I posted. As I pointed out in post #6 the multi-selection dropdown code you posted is localized to H2.