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]

I need to tell you that I have cross-post this question in another forum. I am completely new and did not realise I was making a mistake and breaking the rules. I definitely would have told everyone trying to help me in any forum I posted if a solution would have been found. I'm just in a rush because I'm still at work and it is 22:24 here but I apologise to you and I have apologise to the other person as well and gave him/her the link to this thread so he/she can have a look at our conversation.
The other person in Excel Forum has not found a solution but because the solution you gave me doesn't work for me but it works for you I have then opened 2 new threads in different forums explaining that your code is not working for me and if they could have a look and maybe find out why is not working for me but I didn't include a link to our thread/conversation because I didn't even know that was possible so I apologise and I hope I didn't offend you.

Thanks for understanding and I hope you still try to help me

 

[Serenutty]

I have an idea if you don't mind and accept my apology. I think your code and solution are very clever and I don;t know what else to do to find out why it is not working. But in the other forum that I posted we can upload files. Would you mind looking there? the link is https://www.excelforum.com/excel-pr...a-condition-is-no-longer-met.html#post4815286
And I have uploaded the file there. Maybe you could see what I've done wrong and why it doesn;t work. I certainly would appreciate it

 

[My Aswer Is This]

Well to point out the same rule others have mentioned you’re not supposed to post the same question on two different forums.
Just to clear up what you want let’s talk about this again.
If on sheet named “Client” and you have the code in this sheet you enter the value "Yes" in column “L” then this row of data should be copied to sheet named “Decision”
Now if you go back and change “Yes” in column “L” on sheet named “Client” to something else like “No” or “Duck” then this same row on sheet named “Decision” should be deleted.
The row on sheet named “Client” will never be deleted.
And your using the last script I sent you dated
'Modified 1-5-2018 1:30 PM EST
I want us working with the word “Yes” for now till we figure this out. There should be no mistake using this simple word. Later when it starts working we can change the word back to what you want.
And your saying nothing is now working. The row is never copied to the sheet named “Decision” is that correct?
You need to be sure and answer my questions.

 

[My Aswer Is This]

Here is the new script using the word "Yes"

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 1-5-2018 11:10 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 = "Yes" 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 <> "Yes" 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]

Hi again,
First, thanks for continuing to help me, I really appreciate it. I now know not to post the same question in several forums – I won’t do it again. Thanks for understanding. I’ve also learned not to make a test spreadsheet, you will see why below.
I’m happy to say that your code now works perfectly but strangely I’ve had to open and close the spreadsheet 3 times for it to work correctly, without changing the code.
I don’t know how to express how amazed and thankful I am for your help
I explain the process below because I don’t know if this is normal.
To answer your questions:

1. If on sheet named “Client” and you have the code in this sheet you enter the value "Yes" in column “L” then this row of data should be copied to sheet named “Decision” – Yes
2. Now if you go back and change “Yes” in column “L” on sheet named “Client” to something else like “No” or “Duck” then this same row on sheet named “Decision” should be deleted. – Yes, deleted from sheet 2 “Decision” only
3. The row on sheet named “Client” will never be deleted. – Correct
4. And your using the last script I sent you dated 'Modified 1-5-2018 1:30 PM EST – Yes I was using this one when I said it wsn’t copying but now I’m using the one you sent this morning at Today, 04:13 AM and some of it works. I explain below after answering your questions as requested.
5. I want us working with the word “Yes” for now till we figure this out. There should be no mistake using this simple word. Later when it starts working we can change the word back to what you want. – I understand
6. And your saying nothing is now working. The row is never copied to the sheet named “Decision” is that correct? Yes, that was correct with code 'Modified 1-5-2018 1:30 PM EST but with code Modified Today, 04:13 AM it works but not completely correct. (Wow! I’m really amazed) - I have opened a new spreadsheet and copied the code you sent me in this last email Modified Today, 04:13 AM and changed the legend to "Yes" in “Legend” sheet
I will explain here how it works and what goes wrong:
1. It correctly copies the correct cells from sheet 1 “Client” when “Yes” it’s chosen and pastes them in the right place in sheet “Decision” –Progress J
2. I have created 9 rows in sheet “Client” with “No exiting” and nothing gets copied. This is the correct outcome
3. Then I randomly changed some rows in sheet “Client” with “Yes” and all are copied into sheet “Decision” correctly but when I changed them again to “No exiting” some errors occurred e.g. deleting the title row, or not deleting row 2 from “Decisions”, then when I changed to “No” and back to ”Yes” it copied a new row 2, showing twice in sheet Decisions.
4. So I decided to go row by row in a new fresh version of the spreadsheet to see at what point it went wrong to explain to you
5. I started from row 2 changing all to “Yes” – copied correctly. Row 1 has titles
6. I have changed again row 9 to “No exiting” and deletes it perfectly. I have then changed to “No exiting” rows from 8 to 2 in that order and they have been deleted correctly.
7. Then I tested it again but changing to “No exiting” from 2-9 in that order and they have been deleted correctly
8. Then I’ve changed again random rows to “Yes” and then changed back to “No exiting” – and now it seems to work perfectly.
9. I’ve tried this in 3 different spreadsheets. On the 3^rd one it worked correctly. Wow and double Wow!
Then I transferred your code to my real spreadsheet – Apologies for this but I thought it would be easier to test on another spreadsheet. Now I realise it was a mistake. I’m learning so please bear with me. There’s a copy of the code I have used at the bottom of this message.
I changed to real names “Client” = “Pool” and “Decision” = “Costing” and “Yes” = “Exit from business or transfer to another role outside current BU or Function - no backfill”. It works but the same errors occurred:
First time it wasn’t copying, closed and opened spreadsheet, it copied but at some point deleted the first row with titles in sheet 2 “Decision” = “Costing”.
Closed the spreadsheet and opened again, added title row and it started copying and deleting from sheet “Costing” correctly except for row 2 which was not deleted in sheet “Costing”. I changed the option back and forth several times for row 1 and it copied it twice in “Costing” but when changing from “Yes” to “No” again it deletes one of the rows from record 2 in sheet1 “Pool”.
Opened and closed spreadsheet, it works perfectly except that there are some formulas to the left columns from L column and these formulas are lost and shows as #REF! every time I switch from “Yes” to “No” and viceversa. It even messes up the formula incrementally in rows everytime I switch.
I hope you can shed some light as to why this happens. I hope a formula is not a script because you did ask me if there were any more scripts and said no but I didn’t know any better
[TABLE="width: 671"]
<tbody>[TR]
[TD]Level pre integration
[/TD]
[TD]Talent pooled
[/TD]
[TD]Appointed to ELT-3 role
[/TD]
[TD]New BU or Group Function
[/TD]
[TD]Exiting
[/TD]
[TD]Synergy Saving
[/TD]
[TD]Exit/Transfer Date
[/TD]
[TD]Total cost to achieve
[/TD]
[TD]Annual Savings (£,000's)
[/TD]
[/TR]
[TR]
[TD]ELT-1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]Cost to achieve £'000's
[/TD]
[TD]Annual Savings £'000's
[/TD]
[/TR]
[TR]
[TD]ELT-2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]ELT-3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]ELT-4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]ELT-5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


This is the code I have used for my real spreadsheet:
[Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 1-5-2018 11:10 PM EST
On Error GoTo M
If Target.Count > 1 Then Exit Sub
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets("Costing").Cells(Rows.Count, "XX").End(xlUp).Row + 1
If Target.Column = 12 And Target.Value = "Exit from business or transfer to another role outside current BU or Function - no backfill" Then
Cells(Target.Row, "XX").Value = Now()
Range(Cells(Target.Row, 1), Cells(Target.Row, "H")).Copy Sheets("Costing").Cells(Lastrow, 1)
Sheets("Costing").Cells(Lastrow, "XX").Value = Now()
Else
If Target.Column = 12 And Target.Value <> "Exit from business or transfer to another role outside current BU or Function - no backfill" Then

For Each c In Sheets("Costing").Range("XX1:XX" & Lastrow)
If c.Value = Cells(Target.Row, "XX").Value Then Sheets("Costing").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]

following my previous message:

More results from testing:
I’ve closed spreadsheet and re-opened and made a few changes between No and Yes, these were correct but on the third change when changing the first record (row2) my headings on “Costing” were deleted again.
Then I changed row 2 on “Pool” and was copied on row2 in “Costing” but there were no headings. I further changed row5 from No to Yes and was copied in rwos 1 and 2 in “Costing”
Every time I change from Yes to No it messes up a formula and it doesn’t follow a sequencial order.
Changing from No to Yes does not mess up the formula.

I have discovered the sequence that it follows when messing up the formulas:
Always when changing for yes to No and it skips as many rows as there are in “Costing”
Costing tab
[TABLE="width: 577"]
<tbody>[TR]
[TD]Legacy Company
[/TD]
[TD]Legacy Business Area
[/TD]
[TD]Legacy Business Line or Function
[/TD]
[TD]Role
[/TD]
[TD]Incumbent
[/TD]
[TD]Country
[/TD]
[TD]Location
[/TD]
[TD]Exit Date
[/TD]
[/TR]
[TR]
[TD]AFW
[/TD]
[TD]OGC - DS CP EMEA
[/TD]
[TD]E&I
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]ELT-4
[/TD]
[/TR]
[TR]
[TD]WG
[/TD]
[TD]OGC - DS CP EMEA
[/TD]
[TD]OGC
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]ELT-2
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Pool tab
[TABLE="width: 693"]
<tbody>[TR]
[TD]Level pre integration
[/TD]
[TD]Talent pooled
[/TD]
[TD]Appointed to ELT-3 role
[/TD]
[TD]New BU or Group Function
[/TD]
[TD]Exiting
[/TD]
[TD]Synergy Saving
[/TD]
[TD]Exit/Transfer Date
[/TD]
[TD]Total cost to achieve
[/TD]
[TD]Annual Savings (£,000's)
[/TD]
[/TR]
[TR]
[TD]ELT-1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]Cost to achieve £'000's
[/TD]
[TD]Annual Savings £'000's
[/TD]
[/TR]
[TR]
[TD]ELT-2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Exit from business or transfer to another role outside current BU or Function - no backfill
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]ELT-3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]ELT-4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Exit from business or transfer to another role outside current BU or Function - no backfill
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]ELT-5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Not exiting
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[TD]#REF!
[/TD]
[TD]#REF!
[/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Well this is a very long explanation. Your saying it works sometimes and not other times.
That's not something I have done but something you will have to sort out.
When copying the row to the other sheet it always looks in column A to determine what row to copy it to.
So if the copy to sheet row(2) has no data in column "A"
And this is the only row in the sheet then it will over write what is in row(2)
So if the last row in other sheet with data in column A is row 20 then the new row will be pasted into row 21

So the script assumes there will always be data in column A

Other then that I don't know what to say.

Now if you change sheet names and column "L" values then there will be several places in the script where you will have to modify data.

I would have hoped you would have decided to get the script working with the same sheet names you gave me and using "Yes". And then if this works every time you would know my script worked.

Now later if you modify the sheet names and Column "L" value and things do not work you would know it's because of something you did wrong making the modifications and then you could go back and sort out what you did. Although I'm not trying to place blame on someone.

 

[Serenutty]

I'm definitely not trying to blame you!!! As i said before, I don't know how to express how grateful I am that you have found a solution to my problem.

I did not explain myself well, so I will try now. This is what I mean: I did every test with the code you sent me in my test sprreadsheet. I spotted the errors in the spreadsheet with the "Yes". But after opening and closing 3 times the errors seem to vanish. The test spreadsheet does not have formulas.

Then I decided to transfer the code to the real spreadsheet to see if it works there too. I then realised the real spreadsheet has formulas and these are being lost when changing from "Exit..." to No exiting"

So the problem I have now is a NEW problem that you didn't know it existed before. This is why I have sent you the new names. In case you want solve this problem, then at least we work straight in the real spreadsheet.

Well this is a very long explanation. Your saying it works sometimes and not other times. - Your code now works fine

When copying the row to the other sheet it always looks in column A to determine what row to copy it to. - I understand this
So if the copy to sheet row(2) has no data in column "A"
And this is the only row in the sheet then it will over write what is in row(2)
So if the last row in other sheet with data in column A is row 20 then the new row will be pasted into row 21 - - I understand this too

So the script assumes there will always be data in column A. - I understand this too

Now if you change sheet names and column "L" values then there will be several places in the script where you will have to modify data. - I did change it and it works too. This is why I copied your code with my changes.

I would have hoped you would have decided to get the script working with the same sheet names you gave me and using "Yes". And then if this works every time you would know my script worked. - my test spreadsheet did not have formulas. This is why I thought it would be best to work in the real spreadsheet rather than in my test.

Now later if you modify the sheet names and Column "L" value and things do not work you would know it's because of something you did wrong making the modifications and then you could go back and sort out what you did. Although I'm not trying to place blame on someone. - as I say, your code works fine in both spreadsheets, test with Yes and real with the long wording as choice. The problem has arisen when introducing formulas in columns to the right of column L and between XX (I'm guessing)

Thanks for everything you have done but I hope you don;t give up now since you seem to be a super real expert about this

 

[Serenutty]

I need to be away for a few hours but I hope to hear ffrom you again.
I hope I have explained better this second time
Kind regards
and thank you so much for everything you have done. I f I could I'd send you some flowers, or chocolates or a drink to express my gratitude

 

[My Aswer Is This]

Here is a new script I made so it's easy for you to change sheet names and lookFor values
Start using this new script. You can always know what script is newest by looking on line two of script for date.
You will see the new lines of code marked in red.

You will see :

CopyFrom
CopyTo
LookFor

In my sample I have named them

One
Two
Yes

So all you need to do is change the names

In My example the CopyFrom sheet is named "One"
The CopyTo Sheet is name "Two"

And the LookFor value is "Yes"

So just change.

One
Two
Yes

Change just the values marked in red
To what you want
You will not have to change anything else

As far as copying formulas see that is not something you ever mentioned before until just recently.
That can be very tricky depending on the formulas.

Lets make sure we have all this other stuff sorted out and make sure it's working then we will have to work on formulas.

For example do you want to copy formulas or just the values from the formula?
Please answer this question.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 1-6-2018 6:50 AM EST
On Error GoTo M
If Target.Count > 1 Then Exit Sub
Dim LookFor As String
Dim CopyFrom As String
Dim CopyTo As String
CopyFrom = "[COLOR=#ff0000]One[/COLOR]" 
CopyTo = "[COLOR=#ff0000]Two[/COLOR]"
LookFor = "[COLOR=#ff0000]Yes[/COLOR]"
Sheets(CopyFrom).Activate
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets(CopyTo).Cells(Rows.Count, "A").End(xlUp).Row + 1
    If Target.Column = 12 And Target.Value = LookFor Then
        Cells(Target.Row, "XX").Value = Now()
        Range(Cells(Target.Row, 1), Cells(Target.Row, "H")).Copy Sheets(CopyTo).Cells(Lastrow, 1)
        Sheets(CopyTo).Cells(Lastrow, "XX").Value = Now()
    Else
        If Target.Column = 12 And Target.Value <> LookFor Then
        
            For Each c In Sheets(CopyTo).Range("XX1:XX" & Lastrow)
                If c.Value = Cells(Target.Row, "XX").Value Then Sheets(CopyTo).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