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

 

[Serenutty]

Just arrived to work. PC starting....
Nothing happened when I used my original code, no cells were copied in worksheet 2. So I need to check whether my PC has such an old version of excel that it didn't work out what other cause might be. I'll explain more shortly. PC on now ...

 

[Serenutty]

Hi again,
I'm at work. I've tried original code and it works, phew! Maybe my home excel version is too outdated? Anyway, I've tried your original version, code below, and created a new blank first column in worksheet2 for entering date/time but it's not working because nothing gets copied.

I have now tried your second code (XX) because somehow I found it. And the same as previous - nothing gets copied on worksheet2

Is there any way to send you the workbook? Because maybe I'm missing something really simple when I'm copying

Your response greatly appreciated

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

 

[My Aswer Is This]

Well earlier you said things worked but it deleted rows on the wrong sheet.
And I fixed that so do not know why this would now not work at all.

You should be able to read script and ensure sheet names are correct. You gave me one sheet name but not both sheet names. And value to be entered in column L must be:
"Exit from this plan and entering another"
And one sheet name you gave me was: "Decision"

This script must be put in Other sheet.

 

[My Aswer Is This]

Well one problem we have is you have not given me both sheet names:

You gave me one sheet name. Named "Decision"

I need to know the name of the other sheet.

Do not say sheet2

And you need to use the script that says at the top:

"'Modified 1-5-2018 1:30 PM EST" This is post #8

And you do not need to add any new column.

The script looks in column "L" for the value "Exit from this plan and entering another"

The script needs to be in the sheet which I do not know the name of.

The script puts the date and time in column(XX) on both sheets

 

[Serenutty]

yes, you are correct but the original code I put here dod not have coding row for deleting records, just copying to sheet2.

I enter code on sheet 1 called "Client" by right clicking tab name, pasting your code on window, saving and closing developer. Then I enter records but nothing gets copied.
I know text "Exit from this plan and entering another" makes no sense,but it will always be this, as it is a drop down box with 2 choices; the other choice being "No exiting".
This is just a testing spreadsheet but I'm testing on this spreadsheet.
No other scripts are in the whole spreadsheet.

I can send you the real names and text from the real spreadsheet. I haven't been using that one becasue I didn;t want to mess it up but I'm guessing the same will happen.
Any cahnce I can send you the spreadsheet?
thanks for your help. Really appreciated

 

[Serenutty]

Yes I have found code from "'Modified 1-5-2018 1:30 PM EST" This is post #8
I did understand what it does by your initial explanation
I didn't add any columns for this code.
Sheet 1 is called "Client"
Did you receive my last message explaining how I entered the code?
And is there any way I could send you the spreadsheet for you to check?
I can't use dropbox as it's full and won't allow me to delete anything unless I upgrade

 

[My Aswer Is This]

Well you tell me.

Why was it working when we were not deleting the rows but now your saying it does nothing.

When you say "Then I enter records but nothing gets copied"

You must enter the value "Exit from this plan and entering another" into Column "L" on sheet named "Client"

"Manually". By entering it directly or selecting it from a data validation list.

You cannot copy in 200 rows of data and expect the script to work.

And this workbook must be macro enabled.

 

[Serenutty]

Yes, of course, I enter each record individually and chose "Exit from..." from the data validation list with only 2 choices.
My test spreadsheet is empty and so it's the real spreadsheet
I think I'm doing everything right from my part. Did you try the code on a spreadsheet yourself? Does it work for you?
I'm puzzled

 

[My Aswer Is This]

Sure I test all my scripts. How could I write a script and know it works it I never test it? And I do not know what to tell you.

Are you still saying the original script where we do not delete the row is still copying the rows over?

What version of Excel are you using?
Are you using a Apple computer?

I want you to change the value in column "L" to "Yes" and change the script to look for "Yes" and see if it works.

 

[Serenutty]

Replies below. I know this is frustrating if it works for you but not for me but I don;t know what I'm doing wrong. And all i've done is copy your code and paste it, the same I did with the other code that works fine. I'm sorry. You must be a literal reflection or your image ... banging your head to the table

Are you still saying the original script where we do not delete the row is still copying the rows over? Yes. I have this code in separate spreadsheet and this one still works and copies correctly

What version of Excel are you using? 2016
Are you using a Apple computer? No, I'm using Windows

I want you to change the value in column "L" to "Yes" and change the script to look for "Yes" and see if it works. I have changed it to yes and still doesn't paste. I've changed "Exit from business or transfer to another role outside current BU or Function - no backfill" to "Yes" in the code and "Legend" (sheet3)