Deleting specific cells when a condition is no longer met

[Serenutty]

Hello,

I have this coding below, which works really well, by automatically copying and pasting specific cells from worksheet1 onto worksheet2 when specific words are chosen in worksheet1 cell L from a drop down menu. The problem I have is that once the cells are pasted onto worksheet2, they won’t be deleted if the choice on dropdown menu in worksheet1 cell L changes. Any way to solve this? I thought of adding a subroutine to check for duplicates and delete one as no 2 records will be the same but I have no clue how to add this. Hopefully someone will be able to help me.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim s As String, r, rng As Range, sh As Worksheet
    
    Set sh = Sheets("Decision")
    s = "Exit from this plan and entering another"
    
    If Target.Count > 1 Then Exit Sub
    
    If Target.Column = 12 Then
        If Target = s Then
            r = Target.Row
            Set rng = Range("A" & r & ":H" & r)
            With sh
                rng.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    End If
    
End Sub

 

[My Aswer Is This]

Try this:

See new line of code marked in Red:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim s As String, r, rng As Range, sh As Worksheet
    
    Set sh = Sheets("Decision")
    s = "Exit from this plan and entering another"
    
    If Target.Count > 1 Then Exit Sub
    
    If Target.Column = 12 Then
        If Target = s Then
            r = Target.Row
            Set rng = Range("A" & r & ":H" & r)
            With sh
                rng.Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
            End With
            [COLOR=#ff0000]Rows(r).Delete[/COLOR]
        End If
    End If
    
End Sub

 

[Serenutty]

Hi,
Thanks for your answer. This code deletes the row from worksheet1 and I actually want it deleted from worksheet2 if the choice in worksheet1 is changed and the condition for copying it on worksheet2 is not met anymore. Does this make sense?

 

[My Aswer Is This]

So how will we know what row on sheet "Decisions" will be deleted.
What on the row can identify what row to delete? Is there some unique value on the range copied to the sheet can identify that particular row.

 

[Serenutty]

Ok, We could add a column at the beginning of workshee1 and copied to worksheet2 with a unique number starting with 1 or 0, whichever is best for coding, to know which row to delete - could this number be assigned automatically when a new record is typed in a row?

 

[My Aswer Is This]

See if this will work.

When you activate the script and the row is copied to sheet named "Decision"
Todays date and time will be entered into Column "A" on each sheet.
Now later on if you change the value on sheet (1) Column (12) to anything else the script will now delete that row from sheet(2)

The value date and time will always be unique since you will never copy over two rows at the exact time.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 1-5-2018 11:55 AM EST
If Target.Count > 1 Then Exit Sub
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets("Decision").Cells(Rows.Count, "A").End(xlUp).Row + 1
    If Target.Column = 12 And Target.Value = "Exit from this plan and entering another" Then
        Cells(Target.Row, "A").Value = Now()
        Range(Cells(Target.Row, 1), Cells(Target.Row, "I")).Copy Sheets("Decision").Cells(Lastrow, 1)
    Else
        If Target.Column = 12 And Target.Value <> "Exit from this plan and entering another" Then
    
            For Each c In Sheets("Decision").Range("A1:A" & Lastrow)
                If c.Value = Cells(Target.Row, 1).Value Then Sheets("Decision").Rows(c.Row).Delete
            Next
        End If
    End If
End Sub

If you do not like this. Give me some other plan.
I did now extend the copy range to column (I) since I'm now using Column A for time and date

 

[Serenutty]

Hello My Answer Is
I like it! I like it very much. I am going to try it now but as I'm not at work my pc at home is very slow and I might take a little time. I will let you know when I have tested. Thank you so much

 

[My Aswer Is This]

Lets try this instead.

This script does the same as other script. But it puts the date and time in column(XX)
This way you do not have to modify column "A". I assume you do not already have anything in column (XX)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 1-5-2018 1:30 PM EST
On Error GoTo M
If Target.Count > 1 Then Exit Sub
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets("Decision").Cells(Rows.Count, "XX").End(xlUp).Row + 1
    If Target.Column = 12 And Target.Value = "Exit from this plan and entering another" Then
        Cells(Target.Row, "XX").Value = Now()
        Range(Cells(Target.Row, 1), Cells(Target.Row, "H")).Copy Sheets("Decision").Cells(Lastrow, 1)
        Sheets("Decision").Cells(Lastrow, "XX").Value = Now()
    Else
        If Target.Column = 12 And Target.Value <> "Exit from this plan and entering another" Then
        
            For Each c In Sheets("Decision").Range("XX1:XX" & Lastrow)
                If c.Value = Cells(Target.Row, "XX").Value Then Sheets("Decision").Rows(c.Row).Delete
            Next
            Cells(Target.Row, "XX").Value = ""
        End If
    End If
Exit Sub
M:
MsgBox "Sorry we had some type problem. Try again"
End Sub

 

[Serenutty]

Cool I'll try this one. I have to go back to work because the code doesn't work here at home, even the one that worked before, so I'm getting a little bit panicky. I'll try it as soon a I get there and let you know. Thanks so much for helping me with this.

 

[My Aswer Is This]

Sure. And if things do not work explain what they do wrong. It never helps much when users say "It does not work" without details of what it did or did not do. And do you have other sheet auto event scripts in your sheet because if you have more then one we need to see the other ones.