Events copy and paste

[JOSEPH WEBER]

I have a spreadsheet in which I want to automatically enter a date, and copy
from cells l1:l6 and paste in the same line as cell a2, a3, etc everytime
someone enters information in say cell a2, or a3, and so forth. result
should be cell d2 has a date and e2,f2,...have l1:l6 copied into it. Please
help.

 

[Gord Dibben]

Adding the date to column D is no problem.

Transposing I1:I6 to E2 is no problem.

BUT......................

You want I1:I6 copied into E2:J2 then E3:J3 etc?

You will be overwriting column I cell in each row

Is that really what you want?


Gord Dibben MS Excel MVP

 

[JOSEPH WEBER]

I have a spreadsheet in which cells a:j will have user entered data. I want k
to display the date it was entered and on the same line copy from L1:O1 the
formulas and then paste into the line they are typing in. so if they start
typing in say a3, then in cell k3, there would be a date that populates, then
in L3:O3 would contain the copied formulas from L1:O1. I realize what I made
a mistake in explaining. It didn't make sense to me when I looked at it
again. So can you help?

 

[Gord Dibben]

Yes, I think I can help.

Assumptions...........

J is last column in which users enter data as they work across from A to J
in any row below row 1

Row 1 A to J are titles or similar and data entry starts in Row 2

L1:O1 contain formulas to be copied down to activerow.

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


Gord

 

[JOSEPH WEBER]

This works fine and I thought I could play with some of the format, but it's
not working for me. Specifically, I want this to happen whenever someone puts
info into any cell in column b. row 1 contains all formulas and row 2 is
column titles row 3 would be where data entry starts. The formulas rest in
l11

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
Dim rng1 As Range
Set rng1 = Me.Range("L11")
On Error GoTo enditall
Application.EnableEvents = False
If Target.Row < 3 Then Exit Sub
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value <> "" Then
With Me.Range("K" & n)
.Value = Format(Now, "mm-dd-yyyy hh:mm:ss")
rng1.Copy Destination:=.Offset(0, 1)
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord