Automatic Record Transfer

[Freshman]

Dear experts,

I've a macro before to transfer a record from "A" worksheet to a row below
the last record of another worksheet "Re-activate" by typing "ra" to that
record at 9th column. I'm now further want to transfer another record from
"A" worksheet to another worksheet "Unclaimed" by using the same method above
but by typing "un" at the 9th column, what else should be changed to the
macro below? Please kindly advise.

Thanks in advance.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Column = 9 And Target.Value = "ra" Then
Dim eRow As Long
eRow = Sheets("Re-activate").Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(Target.Row, 1), Cells(Target.Row, 9)).Copy _
Sheets("Re-activate").Cells(eRow, 1)
Rows(Target.Row).Delete
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

 

[Jacob Skaria]

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim eRow As Long, ws As Worksheet

If Target.Count = 1 And Target.Column = 9 Then

If Target.Value = "ra" Then
Set ws = Sheets("Re-activate")
ElseIf Target.Value = "un" Then
Set ws = Sheets("Unclaimed")
End If

If Not ws Is Nothing Then
Application.EnableEvents = False
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(Target.Row, 1), Cells(Target.Row, 9)).Copy ws.Cells(eRow, 1)
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End If
End Sub

 

[Freshman]

Hi Jacob,

It works wonderfully. Thanks again.

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim eRow As Long, ws As Worksheet

If Target.Count = 1 And Target.Column = 9 Then

If Target.Value = "ra" Then
Set ws = Sheets("Re-activate")
ElseIf Target.Value = "un" Then
Set ws = Sheets("Unclaimed")
End If

If Not ws Is Nothing Then
Application.EnableEvents = False
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(Target.Row, 1), Cells(Target.Row, 9)).Copy ws.Cells(eRow, 1)
Rows(Target.Row).Delete
Application.EnableEvents = True
End If

End If
End Sub