#### Skybluekid

##### Well-known Member

- Joined
- Apr 17, 2012

- Messages
- 829

Hi All,

I am very stumped at the moment. I am using the below code to copy cells if the Sales person selects an option:

This works well, but I have a twist. If the Sales has chosen Hot originally, then changes the value to Won (or Lost), I will need it find the deal on the Hot Deals Sheet, delete that row. It should also then copy the cells to the Won (or Lost, as the case maybe) Sheet.

I have looked at various options but none seem to work.

Thank you in Advance as any help would be very appreciated.

I am very stumped at the moment. I am using the below code to copy cells if the Sales person selects an option:

Code:

```
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim s As String
Dim Rng(1 To 8)
Dim NV As String
Dim OV As String
Dim FV As String
Dim Rng2 As Range
Dim Cell As Range
Dim Old
'Execute code if value in twentieth column is changed
If Target.Column = 20 Then
'If value in T column of the row is "Won" then copy the row to destination sheet
If Target.Value = "Won" Then
Rng(1) = "A" & Target.Row
Rng(2) = "B" & Target.Row
Rng(3) = "C" & Target.Row
Rng(4) = "E" & Target.Row
Rng(5) = "G" & Target.Row
Rng(6) = "K" & Target.Row
Rng(7) = "L" & Target.Row
Rng(8) = "M" & Target.Row
s = Join(Rng, ",")
Set R = Range(s)
R.Copy Destination:=Sheets("Won Business").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
'If value in T column of the row is "Lost" then copy the row to destination sheet
If Target.Value = "Lost" Then
Rng(1) = "A" & Target.Row
Rng(2) = "B" & Target.Row
Rng(3) = "C" & Target.Row
Rng(4) = "E" & Target.Row
Rng(5) = "G" & Target.Row
Rng(6) = "K" & Target.Row
Rng(7) = "L" & Target.Row
Rng(8) = "M" & Target.Row
s = Join(Rng, ",")
Set R = Range(s)
R.Copy Destination:=Sheets("Lost Business").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
'If value in T column of the row is "Hot" then copy the row to destination sheet
If Target.Value = "Hot" Then
Rng(1) = "A" & Target.Row
Rng(2) = "B" & Target.Row
Rng(3) = "C" & Target.Row
Rng(4) = "E" & Target.Row
Rng(5) = "G" & Target.Row
Rng(6) = "K" & Target.Row
Rng(7) = "L" & Target.Row
Rng(8) = "M" & Target.Row
s = Join(Rng, ",")
Set R = Range(s)
R.Copy Destination:=Sheets("Hot Deals").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End If
End Sub
```

This works well, but I have a twist. If the Sales has chosen Hot originally, then changes the value to Won (or Lost), I will need it find the deal on the Hot Deals Sheet, delete that row. It should also then copy the cells to the Won (or Lost, as the case maybe) Sheet.

I have looked at various options but none seem to work.

Thank you in Advance as any help would be very appreciated.

Last edited by a moderator: