Copy date from row 1 to last row next sheet
Need some help I am currently using this macro -
Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long, ws As Worksheet If Not Target.Address = "$A$1" Then Exit Sub Set ws = Worksheets("sheet2") LastRow = ws.Cells(Rows.Count, "c").End(xlUp).Row ws.Range("c" & LastRow + 1).Value = Target.Value End Sub Works great for coping one cell. But how can i increase the copy area - I need to copy Cells A1:N1 to sheet2 last row C1:P1 when ever sheet1 A1:N1 are changed. I could actaully use N1 as the triger if needed. Set up is an out side programm will be pasting data to sheet1 A1:N1 for each record, I need to then copy that row of information to create a list on another worksheet. Thanks to anyone that can help. |
Copy date from row 1 to last row next sheet
Try this macro. Note that this macro will copy A1:N1 to "C" & the last row
EACH TIME that ANY ONE of the A1:N1 cells is changed. Come back if this is not what you want. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long If Not Intersect(Target, Range("A1:N1")) Is Nothing Then With Worksheets("Sheet2") LastRow = .Cells(Rows.Count, "c").End(xlUp).Row Range("A1:N1").Copy .Range("c" & LastRow + 1) End With End If End Sub "MyKeyJ" wrote in message ... Need some help I am currently using this macro - Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long, ws As Worksheet If Not Target.Address = "$A$1" Then Exit Sub Set ws = Worksheets("sheet2") LastRow = ws.Cells(Rows.Count, "c").End(xlUp).Row ws.Range("c" & LastRow + 1).Value = Target.Value End Sub Works great for coping one cell. But how can i increase the copy area - I need to copy Cells A1:N1 to sheet2 last row C1:P1 when ever sheet1 A1:N1 are changed. I could actaully use N1 as the triger if needed. Set up is an out side programm will be pasting data to sheet1 A1:N1 for each record, I need to then copy that row of information to create a list on another worksheet. Thanks to anyone that can help. |
Copy date from row 1 to last row next sheet
works good, except the data is entered one cell at a time, and i am thinking
if it would wait till the last cell (N1) is updated then copy the row A1:N1 to sheet2 would be much more practical. Otto thanks for your help. MJ. "Otto Moehrbach" wrote: Try this macro. Note that this macro will copy A1:N1 to "C" & the last row EACH TIME that ANY ONE of the A1:N1 cells is changed. Come back if this is not what you want. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long If Not Intersect(Target, Range("A1:N1")) Is Nothing Then With Worksheets("Sheet2") LastRow = .Cells(Rows.Count, "c").End(xlUp).Row Range("A1:N1").Copy .Range("c" & LastRow + 1) End With End If End Sub "MyKeyJ" wrote in message ... Need some help I am currently using this macro - Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long, ws As Worksheet If Not Target.Address = "$A$1" Then Exit Sub Set ws = Worksheets("sheet2") LastRow = ws.Cells(Rows.Count, "c").End(xlUp).Row ws.Range("c" & LastRow + 1).Value = Target.Value End Sub Works great for coping one cell. But how can i increase the copy area - I need to copy Cells A1:N1 to sheet2 last row C1:P1 when ever sheet1 A1:N1 are changed. I could actaully use N1 as the triger if needed. Set up is an out side programm will be pasting data to sheet1 A1:N1 for each record, I need to then copy that row of information to create a list on another worksheet. Thanks to anyone that can help. |
Copy date from row 1 to last row next sheet
How about this? This macro will do the copying/pasting only if an entry is
made in N1. Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long If Not Intersect(Target, Range("N1")) Is Nothing Then With Worksheets("Sheet2") LastRow = .Cells(Rows.Count, "c").End(xlUp).Row Range("A1:N1").Copy .Range("c" & LastRow + 1) End With End If End Sub "MyKeyJ" wrote in message ... works good, except the data is entered one cell at a time, and i am thinking if it would wait till the last cell (N1) is updated then copy the row A1:N1 to sheet2 would be much more practical. Otto thanks for your help. MJ. "Otto Moehrbach" wrote: Try this macro. Note that this macro will copy A1:N1 to "C" & the last row EACH TIME that ANY ONE of the A1:N1 cells is changed. Come back if this is not what you want. HTH Otto Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long If Not Intersect(Target, Range("A1:N1")) Is Nothing Then With Worksheets("Sheet2") LastRow = .Cells(Rows.Count, "c").End(xlUp).Row Range("A1:N1").Copy .Range("c" & LastRow + 1) End With End If End Sub "MyKeyJ" wrote in message ... Need some help I am currently using this macro - Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long, ws As Worksheet If Not Target.Address = "$A$1" Then Exit Sub Set ws = Worksheets("sheet2") LastRow = ws.Cells(Rows.Count, "c").End(xlUp).Row ws.Range("c" & LastRow + 1).Value = Target.Value End Sub Works great for coping one cell. But how can i increase the copy area - I need to copy Cells A1:N1 to sheet2 last row C1:P1 when ever sheet1 A1:N1 are changed. I could actaully use N1 as the triger if needed. Set up is an out side programm will be pasting data to sheet1 A1:N1 for each record, I need to then copy that row of information to create a list on another worksheet. Thanks to anyone that can help. |
All times are GMT +1. The time now is 01:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com