Automatic Record Transfer

F

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
 
J

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
 
F

Freshman

Hi Jacob,

It works wonderfully. Thanks again.

Jacob Skaria said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top