input info when pasted in cells

[kardifflad]

Hello.
I have this bit of code thats set as a change event in my spreadhseet
So basically when something is entered in column A it updates inf
columns D,E,F,G. (so i can see who did something and when).
However this only work if the user types info in but not if they cop
and paste a load in at a time. So for example if they pasted in 10
entries into column A, then the other columns won't update.
how can i change the code to deal with pasting? (i have tried th
different change events in the spreadsheet coding but nothing works
Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A:A")) Is Nothing Or Target.Cells.Coun

1 Then Exit Sub

Target.Offset(0, 2).Value = "OBJECT"
Target.Offset(0, 3).Value = Application.UserName
Target.Offset(0, 4).Value = Date
Target.Offset(0, 5).Value = Range("B2")
Target.Offset(0, 6).Value = Date + 160


End Su

 

[Claus Busch]

Hi,

Am Tue, 18 Dec 2012 18:31:55 +0000 schrieb kardifflad:

If Intersect(Target, Range("A:A")) Is Nothing Or Target.Cells.Count

change to :
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub


Regards
Claus Busch

 

[Ben McClave]

Hello,

Two other quick recommendations. First, you may want to disable events while this code runs so that the cells being updated don't continually run theprocedure. The other suggestion is to change the "Target.Offset..." line to "Intersect(Target, Range"A:A")).Offset...". This way, if someone pastesdata into a range that includes not only column A, but other columns as well, the offset formula will keep the tracking information in the proper columns.

Here is an updated procedure to illustrate:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Intersect(Target, Range("A:A"))
.Offset(0, 2).Value = "OBJECT"
.Offset(0, 3).Value = Application.UserName
.Offset(0, 4).Value = Date
.Offset(0, 5).Value = Range("B2")
.Offset(0, 6).Value = Date + 160
End With
Application.EnableEvents = True
End Sub

 

[kardifflad]

thank you. This worked excellent until i run another macro in m
workbook. The other macro copies all the data from this book int
another one, after it has copied the data it then deletes it from th
current workbook by using .Clearcontents.
however when it clears the contents the worksheet change code youv
given me kicks in and enters data in the offset cells in the same way i
would if data was copied into them.
so for some reason when clearing contents it is also thinking data i
being entered. Is that easily fixable or should i perhaps look to remov
this code from my worksheet change code and instead have thi
onformation updated when i run the macro that copies the data?? (i
that makes sense)

Thank yo

 

[Ben McClave]

Hello,

All you'll need to do is disable events right before the .ClearContents line and then enable them again right after it. For example:

Application.EnableEvents = False
Range("A:A").ClearContents
Application.EnableEvents = True

 

[kardifflad]

oh course. how simple!! a Homer Simpson moment me thinks.

Thanks for your help