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 3rd 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]