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

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
<(E-Mail Removed)> 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
>

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
>
>