Automatically Copy Information when data is entered
 

I have the following code in events of one tab of a spreadsheet. It copies
from the line above. I need it to copy a formula in the cells L1:V1. Please
show me where I should change the code to make it accomplish this task.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col J
On Error GoTo enditall
Application.EnableEvents = True
If Target.Cells.Column = 10 Then
N = Target.Row
If Me.Range("j" & N).Value < "" Then
With Me.Range("L" & N)
.Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
.Offset(-1, 1).Resize(1, 10).Copy Destination:=.Offset(0, 1)



End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Automatically Copy Information when data is entered
 

Do you mean copy formulas "from" L1:V1 to active row?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col J
Dim rng As Range
Set rng = Me.Range("L1:V1")
On Error GoTo enditall
Application.EnableEvents = True
If Target.Cells.Column = 10 Then
N = Target.Row
If Me.Range("j" & N).Value < "" Then
rng.Copy Destination:=Me.Range("L" & N)
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 May 2010 07:31:01 -0700, JOSEPH WEBER
wrote:

I have the following code in events of one tab of a spreadsheet. It copies
from the line above. I need it to copy a formula in the cells L1:V1. Please
show me where I should change the code to make it accomplish this task.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col J
On Error GoTo enditall
Application.EnableEvents = True
If Target.Cells.Column = 10 Then
N = Target.Row
If Me.Range("j" & N).Value < "" Then
With Me.Range("L" & N)
.Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
.Offset(-1, 1).Resize(1, 10).Copy Destination:=.Offset(0, 1)



End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Automatically Copy Information when data is entered
 

Thanks so much. that makes life ten times easier. Works perfectly

"JOSEPH WEBER" wrote:

I have the following code in events of one tab of a spreadsheet. It copies
from the line above. I need it to copy a formula in the cells L1:V1. Please
show me where I should change the code to make it accomplish this task.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col J
On Error GoTo enditall
Application.EnableEvents = True
If Target.Cells.Column = 10 Then
N = Target.Row
If Me.Range("j" & N).Value < "" Then
With Me.Range("L" & N)
.Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
.Offset(-1, 1).Resize(1, 10).Copy Destination:=.Offset(0, 1)



End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub