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]

Hi, just wanted to say that I have received your message. I will reply later as I'm away from my PC and not sure what the formulas are but I think they are in sheet 2 and the value transferred to sheet 1. My guess would be to save the formula,v but I have to check first. Thanks so much. And yes I agree let's fix one problem first and then the formulas. Thank you so much for your help

 

[Serenutty]

Hi, just wanted to say that I have received your message. I will reply later as I'm away from my PC and not sure what the formulas are but I think they are in sheet 2 and the value transferred to sheet 1. My guess would be to save the formula,v but I have to check first. Thanks so much. And yes I agree let's fix one problem first and then the formulas. Thank you so much for your help

You are a genius!!
I have tried you latest code and it works perfectly on my real workbook after changing names.

Regarding formulas, yes I'm sorry I didn't mention before. I didn't know it myself at the beginning. Original workbook was modified after I (or should I say you!) started working on it.

There are only 3 formulas, all of them are in sheet One.
These are:

Sheet One: Column M: =IF(L2="Exit from business or transfer to another role outside current BU or Function - no backfill",1,0)

Sheet One: Column O: =Costing!Y2

Sheet One: Column P: =Costing!R2

The error #REF ! =Costing!#REF !

Simple formulas and based on what they are I think the formula in sheet One needs to be kept as the values in Sheet Two can change. Do you agree?

There are no other formulas in the whole workbook (so far!)

Kind regards and again thank you so much for helping with this. I really have forgotten all about coding but seeing how you solve things is helping me understand. I should start learning the basics again

 

[My Aswer Is This]

Well we are not really changing any thing in sheet One. We are just copying the row over to sheet Two.
And then we are deleting the row in Sheet Two later on if you change the value in column "L"
So I'm assuming your happy with what we have now?
Am I correct?

A lot of Vba code is written in such a way as if you read English and I assume Excel is also written in other languages also it's easy to understand.
Microsoft I think did a good job making their code fairly easy to read I think.
For example:

Sheets(1).Range("A2").value="Sam"

To me that's easy to understand

Or something like:

If Range("A1").value="Donald" Then Range("B1").value="Duck"
Or:
Here is best way to do this:
Sheets(1).Columns(1).Copy Sheets(2).Columns(4)

Some people like using select too much.

Some people want to do something like this:

Sheets(1).Select
Columns(1).Select
Selection.Copy
Sheets(2).Select
Columns(4).Select
Selection.PasteSpecial



But we all learn as we go.
Glad your learning more every day about Excel and Vba.

But I can assure you there are a lot of folks here who can do things better then me.

Take care and thanks for you kind comments.
Hope your not feeling bad weather where you are today. A lot of bad weather today around the World. We are freezing here today in Central Florida USA. We think anything below 70 degrees F is cold. At this moment it's 42 degrees F at 10:55 AM EST

Glad I was able to help you.

If you still need something changed explain what else we need.

 

[Serenutty]

Sorry, I don;t know how but I missed your last response.

It would be helpful if you fixed the loss of formula reference. Do you think that a simple solution for the loss of formula reference would be to copy the cells containing formulas too? Just a thought!

I will read your comments of simple language and see if I understand it hehe. i know that a few years back when I was learning I could understand code ;anguage bette than proper English because it's direct and simple. No innuendos, double meanings, etc

Yes, I read that it snowed in Florida, first time in 28 years! I also read that Iguanas are falling from trees because they are so cold they get imovilised.
I lived in Miami for a few years but I left to come to England! Oh how I regret that now!
Anyway hope to hear from you soon

 

[Serenutty]

Sorry, I don;t know how but I missed your last response.

It would be helpful if you fixed the loss of formula reference. Do you think that a simple solution for the loss of formula reference would be to copy the cells containing formulas too? Just a thought!

I will read your comments of simple language and see if I understand it hehe. i know that a few years back when I was learning I could understand code ;anguage bette than proper English because it's direct and simple. No innuendos, double meanings, etc

Yes, I read that it snowed in Florida, first time in 28 years! I also read that Iguanas are falling from trees because they are so cold they get imovilised.
I lived in Miami for a few years but I left to come to England! Oh how I regret that now!
Anyway hope to hear from you soon
By the way I think you are a genius and more patient and knowledgeable than most people here, which probably makes you better every time

 

[My Aswer Is This]

The way I'm doing it now does copy the formulas in the range we are copying. To sheet Two
You click in the cell and look up at the Formula Bar and you will see the formula is in the cell.
But in the cell you may not be getting what you want.
But if I just copy over the results of the formula you will see the same thing in sheet two which you saw in Sheet one.

So do you want the formula copied over or the results of the formula.

I know this may be hard to understand.

 

[Serenutty]

Yes you are right. It copies the formula when L= "Exit ..."
But when I change L from "Exit ..." to " No Exiting" the formula isn't copied in sheet 1 and returns error =Costing!#REF! (losses the reference to the cell =Sheet 2! P2)
I think we need to keep the formula so it references to the right cell. I suppose it loses it because the row changes?
How can we keep the formula pointing to the right cell (column and row?)
Thsi is a mystery to me

 

[Serenutty]

I've also noticed that the values that are copied do not keep the row they correspond to

 

[My Aswer Is This]

As far as I can see we are changing nothing in sheet one except for the value in column "L" which you are doing.
All we are doing is copying the range into sheet two.

Now since I do not know why we are doing this or what is happening to the data after it gets in sheet two I would not know what to say.

I normally do not ask why someone wants to do something I just try to do what they want.

Now some users will tell us what their ultimate goal is and will ask how best to achieve that goal. But that was not what you did.

Maybe you would like to explain more about why your doing this. And what is your ultimate goal.

Some times it's to store certain data on one sheet as a historic record where it stays.
Now if later on you want to delete the row on sheet two because you have made new changes to that row and you want to delete the old row on sheet 2 and then copy the same row over again that's a good plan.

But for some reason when you change the value in column "L" this is also changing other things I suppose.

So if you want to explain more maybe I can help you more

 

[My Aswer Is This]

I've also noticed that the values that are copied do not keep the row they correspond to

That is true. If on sheet(1) you change the value in column "L" that row is copied to sheet two but will not in most cases be copied to the same row. You never asked for that as far as I remember. It copies it to the first empty row in sheet 2.