Automatically move cell lines to multiple sheets

[JessicaKinnear]

Hello, first off please bare with me if this is a stupid question, this is my first time ever putting any code in so I am very much a beginner.

On my spreadsheet I would like to be able to use the function 'If the data in column 8 contains "ü" then move it to sheet 2 and delete the row', but I'd also like it to do 'If the data in column 9 contains "ü" then move it to sheet 3 and delete the row.

I found a previous post where someone wrote the code for just actioning one point e.g.:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("sheet 2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    End If
End Sub

So I thought I could just copy and paste the above below but change the numbers from 8 to 9 and the sheet from 2 to 3 see below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 8 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("pitches").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    End If
End Sub
    If Target.Column = 9 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("Dead leads").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
             End With
        End If
    End If
End Sub

However I get the comment 'Only comments may appear after End Sub, End Function, or End Property'

Any ideas how I can do this so the sheet runs both actions?

Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 8 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("pitches").Range("A" & Rows.count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    ElseIf Target.Column = 9 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("Dead leads").Range("A" & Rows.count).End(xlUp).Offset(1, 0)
                .Delete
             End With
        End If
    End If
End Sub

 

[JessicaKinnear]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 8 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("pitches").Range("A" & Rows.count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    ElseIf Target.Column = 9 Then
        If LCase(Target.Value) = "ü" Then
            With Target.EntireRow
                .Copy Sheets("Dead leads").Range("A" & Rows.count).End(xlUp).Offset(1, 0)
                .Delete
             End With
        End If
    End If
End Sub

[/CODE
[/QUOTE]

Perfect - thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JessicaKinnear]

You're welcome & thanks for the feedback.

Sorry another question, if I wanted to do exactly the same as above but instead of the cell containing '"ü"', I would like it to move the row if the cell contains any date in the format dd/mm/yyyy. I've tried replacing '"ü" in the code above with '"dd/mm/yyyy" but no luck. - Thanks in advance

 

[Fluff]

Try

   If Target.Value Like "##/##/####" Then

 

[JessicaKinnear]

Again perfect - thank you!

 

[Fluff]

My pleasure.