'Object required' code error

[Trick]

Hi All,

Trying to do a Macro (I think that's the correct terminology) but it comes up with 'Target.Column <Object Required>

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

 

[MARK858]

You had a few other errors, try...

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

 

[dmt32]

Hi, welcome to forum.
Looking at your code I suspect the error occurs because you have deleted the Target range in previous If test - maybe

Try this update to the code & see if does what you want

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim m As Variant
    With Target
        If .Column = 2 And .Cells.Count = 1 Then
            m = Application.Match(.Value, Array("Male", "Female"), 0)
            If Not IsError(m) Then
                .EntireRow.Copy Sheets(Choose(Val(m), "Males", "Females")).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .EntireRow.Delete
            End If
        End If
    End With
End Sub

Dave

 

[Trick]

Omg thank you heaps!!!!
ElseIf =D

 

[MARK858]

Omg thank you heaps!!!!
ElseIf =D

Happy it helped and welcome to the board from me as well.

Just to point out the other error that you had in your original code was you had and End(x1Up) which should be End(xlUp)... a lowercase L not a 1 (I also thought that you had the wrong number of End Ifs for the number of If's but that doesn't appear to be the case now).

I notice that a Moderator has amended your first post to put code tags around the code to make it easier to read and copy (and indented it).
It might be worth considering using code tags in any future posts (some ways to do this are in my signature block below)

 

[RoryA]

I'd also suggest you read the forum rules on cross-posting, please.