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 info 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 copy and paste a load in at a time. So for example if they pasted in 100 entries into column A, then the other columns won't update.
how can i change the code to deal with pasting? (i have tried the 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.Count 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 Sub

Hi,

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

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

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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 the procedure. The other suggestion is to change the "Target.Offset..." line to "Intersect(Target, Range"A:A")).Offset...". This way, if someone pastes data 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

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

Thank you

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

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

Thanks for your help.