Automatic Record Transfer
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 |
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 |
Automatic Record Transfer
Hi Jacob,
It works wonderfully. Thanks again. "Jacob Skaria" wrote: 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 |
All times are GMT +1. The time now is 05:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com