Automatic Record Transfer
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
--
Jacob
"Freshman" wrote:
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
|