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

slayer1957

New Member
Joined
Jan 9, 2017
Messages
25
Good day,

I have code,
[HR][/HR]
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
[HR][/HR]


-----------------------------------------------
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
Try something like this.
Code:
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

New Member
Joined
Jan 9, 2017
Messages
25
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,541
Office Version
365
Platform
Windows
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'.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,146
Messages
5,448,601
Members
405,522
Latest member
NomanAziz

This Week's Hot Topics

Top