Move row to specified sheets based on multiple cell values in column F

[slayer1957]

Good day,

I have code,

---

Private Sub Worksheet_Change(ByVal Target As Range)
'Version 2
If Not Intersect(Target, Range("F:F")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Completed").Cells(Rows.Count, "F").End(xlUp).Row + 1


If Target.Value = "Yes" Then
Rows(Target.Row).Copy Destination:=Sheets("Completed").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If
End Sub

---

-----------------------------------------------
With this code on the current sheet it checks the value of cell F in the row, if it is "Yes" it moves the entire row to sheet "Completed" and delete the row on the source sheet. I want to expand the code, when cell F in the row has "Parking Bay" it must move entire row to sheet "Parking Bay". Both the Yes and Parking Bay must work.

Please assist to change

 

[Norie]

Try something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
'Version 2
Dim wsDst As Worksheet
Dim Lastrow As Long

    If Not Intersect(Target, Range("F:F")) Is Nothing Then
    
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        
        Select Case Target.Value
            Case "Yes"
                Set wsDst = Sheets("Completed")
            Case "Parking Bay"
                Set wsDst = Sheets("Parking Bay")
        End Select
        
        If Not wsDst Is Nothing Then
        
            With wsDst
                Lastrow = .Cells(Rows.Count, "F").End(xlUp).Row + 1
                Rows(Target.Row).Copy Destination:=.Rows(Lastrow)
            End With
            
            Rows(Target.Row).Delete
        
        End If
    
    End If

End Sub

 

[slayer1957]

The code works fine, I have changed to word "Parking Bay" to "Review", It does not work however when the sheet name "Parking Bay" and word has two words, "Parking Bay", but when changed to "Review" it works.

Thanks for the help

 

[Norie]

It shouldn't matter how many words there are typed in the cell or in the sheet name, if you enter 'Parking Bay' in column F the row should be copied to the sheet named 'Parking Bay'.