How to move row to another sheet?-CodePudding

VBA is a total black magic to me so please be gentle and assume you're talking to an idiot :)

I've created a spreadsheet, with multiple tabs. Based on a drop down selection, I am trying to move rows to corresponding sheets.

I've achieved that ( and is working great ) by using the below VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    If Target.Column = 3 And Target.Row > 7 And (Target.Value = "LTS" Or Target.Value = "On Hold") Then
        Application.EnableEvents = False
'       Copy columns A to AU to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("On Hold and LTS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    If Target.Column = 3 And Target.Row > 7 And (Target.Value = "Leaver") Then
        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("Leavers").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub

Although the above code works as intended, every time I receive the "run time error 424 - object required message". Clicking end resolves it and the rule works as intended.

( The second part of the code [ Leaver ] does not produce the error, and also works as intended )

Could you please help me stopping this error from coming up please?

Thank you!

CodePudding user response：

So the above revised code worked and I am no longer getting the error!

Instead, I am now getting an error when performing the action in reverse i.e transferring a row from say "LTS" sheet back to "Matrix". ( It worked fine before )

No sure if this screenshot helps in any way? Error line

CodePudding user response：

If the cell contains "LTS" (or "On Hold") then the first If..End If block runs and among other things deletes the row containing Target. You then move to your second If..End If block and test Target - which no longer exists - hence your error.

Try using ElseIf in there instead:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see only one cell updated
    If Target.CountLarge > 1 Then Exit Sub
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    If Target.Column = 3 And Target.Row > 7 And (Target.Value = "LTS" Or Target.Value = "On Hold") Then

        Application.EnableEvents = False
'       Copy columns A to AU to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("On Hold and LTS").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    
'   Check to see if entry is made in column C after row 7 and is set to "Yes"
    ElseIf Target.Column = 3 And Target.Row > 7 And (Target.Value = "Leaver") Then

        Application.EnableEvents = False
'       Copy columns B to I to complete sheet in next available row
        Range(Cells(Target.Row, "A"), Cells(Target.Row, "AU")).Copy Sheets("Leavers").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'       Delete current row after copied
        Rows(Target.Row).Delete
        Application.EnableEvents = True
    End If
    
End Sub