Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Auto Record Transfer
Dear all,
I've a workbook with VBA code (written by an expert in this discussion group - Roman) for tranferring records from one worksheet to another. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Count = 1 And Target.Column = 4 And Target.Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy _ Sheets("Completed").Cells(eRow, 1) Rows(Target.Row).Delete End If ErrorHandler: Application.EnableEvents = True End Sub My question is, if I want to transfer several rows of records to another sheet in one time, after I typed "Y" in one row and copied down to other cells in the same column by dragging the cell handle, only the first record is transferred. Other rows with "Y" have no action and remain in the first worksheet. Any methods can help? Please kindly advise. Thanks in advance. |
#2
|
|||
|
|||
Auto Record Transfer
Freshman,
Try the version below. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub "Freshman" wrote in message ... Dear all, I've a workbook with VBA code (written by an expert in this discussion group - Roman) for tranferring records from one worksheet to another. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Count = 1 And Target.Column = 4 And Target.Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy _ Sheets("Completed").Cells(eRow, 1) Rows(Target.Row).Delete End If ErrorHandler: Application.EnableEvents = True End Sub My question is, if I want to transfer several rows of records to another sheet in one time, after I typed "Y" in one row and copied down to other cells in the same column by dragging the cell handle, only the first record is transferred. Other rows with "Y" have no action and remain in the first worksheet. Any methods can help? Please kindly advise. Thanks in advance. |
#3
|
|||
|
|||
Auto Record Transfer
Dear Bernie,
Thanks for your revised code and it works prefectly. Thanks once again. Freshman "Bernie Deitrick" wrote: Freshman, Try the version below. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myCell As Range On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Column = 4 And Target(1).Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 For Each myCell In Target myCell.Offset(0, -3).Resize(, 4).Copy _ Sheets("Completed").Cells(eRow, 1) eRow = eRow + 1 Next myCell Target.EntireRow.Delete End If ErrorHandler: Application.EnableEvents = True End Sub "Freshman" wrote in message ... Dear all, I've a workbook with VBA code (written by an expert in this discussion group - Roman) for tranferring records from one worksheet to another. The code is as follows: Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Count = 1 And Target.Column = 4 And Target.Value = "Y" Then Dim eRow As Long eRow = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp).Row + 1 Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Copy _ Sheets("Completed").Cells(eRow, 1) Rows(Target.Row).Delete End If ErrorHandler: Application.EnableEvents = True End Sub My question is, if I want to transfer several rows of records to another sheet in one time, after I typed "Y" in one row and copied down to other cells in the same column by dragging the cell handle, only the first record is transferred. Other rows with "Y" have no action and remain in the first worksheet. Any methods can help? Please kindly advise. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't go to 'Last Record' in one step in a Word doc. linked To Exc | Excel Discussion (Misc queries) | |||
Auto Data Transfer | Excel Worksheet Functions | |||
Help - now really stuck! File transfer problem | Excel Discussion (Misc queries) | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |