Move data from one sheet to another if a condition is met

[Pezzy]

I need to set up a Marco so that data from one worksheet (In-House Repairs) gets transferred to an identical second worksheet (Completed) when a 'Yes' selection is made in C16 on the main worksheet via a drop down selection "Yes/No". The entries need to be deleted from the main worksheet and copied to the Completed worksheet as an archive so I can see what I have currently in for repair.

Can anyone help? Preferably something with a safeguard where you need to double click somewhere to activate the macro perhaps so that entries don't just disappear if someone accidentally sets some of the entries to 'Yes'. The document will be used by multiple people.

 

[mumps]

It is hard to work with a picture. 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. Please include some sample data in 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).

 

[Pezzy]

It is hard to work with a picture. 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. Please include some sample data in 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).

Link to doc - Service Document 2023 Test.xlsx

It's really simple. Two identical worksheets. If you select 'Yes' on the main worksheet, that entire row should be copied over to the second worksheet and deleted from the first.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "In-House Repairs" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim desWS As Worksheet
    Set desWS = Sheets("Completed")
    If Target = "Yes" Then
        Target.EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Pezzy]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "In-House Repairs" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim desWS As Worksheet
    Set desWS = Sheets("Completed")
    If Target = "Yes" Then
        Target.EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        Target.EntireRow.Delete
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Thank you for the help so far. I've tried the code, but it's not doing anything. I saved the document with the changes and went back in, but still nothing.

 

[mumps]

Did you place the macro in the worksheet code module as I described and not in a standard module? It worked on the file you posted.

 

[Pezzy]

Did you place the macro in the worksheet code module as I described and not in a standard module? It worked on the file you posted.

Just realized your code is for a change in C3 when it should be C16. I've updated the code and it's working like a charm. Thanks!

Is there a way to build in a safeguard where once the selection is made it will warn you, or where you have to double click in a field etc?

 

[Pezzy]

Just realized your code is for a change in C3 when it should be C16. I've updated the code and it's working like a charm. Thanks!

Is there a way to build in a safeguard where once the selection is made it will warn you, or where you have to double click in a field etc?

Do you know what, I've copied and amended the code for the second worksheet to transfer the entries back to the main worksheet if you select 'No', just so there's an easy way to get the entries back.

Thanks for the help, it is very much appreciated.

 

[mumps]

Change the warning message (in red) to suit your needs. The macro as written will run with a change in any cell in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    If MsgBox("Are you sure you want to copy the data to the 'Completed' sheet?", vbYesNo) = vbYes Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Dim desWS As Worksheet
        Set desWS = Sheets("Completed")
        If Target = "Yes" Then
            Target.EntireRow.Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub