Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by Date and Copy results to another sheet | New Users to Excel | |||
COPY SAME DATE/CELL TO EVERY SHEET? | Excel Discussion (Misc queries) | |||
how do i copy various sheets date to one sheet in the same excel . | Excel Worksheet Functions | |||
How to copy records containing a specific date range to new sheet? | Excel Worksheet Functions | |||
Copy data from sheet 1 to sheet 2 based on day/date | Excel Programming |