Solved: worksheet_change event does not trigger on copy/paste

[tools]

Hi all,

when i paste some values (rows) from one excel sheet to another the worksheet_change event is not triggered properly

Here is the code that i have written
Is there any other event which could trigger this .?


[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Unprotect

On Error Resume Next
Dim rFound As Range
Dim rowlast
If Not Intersect(Target, Range("D4,U4,C10:C100,E10:E100,G10:G100")) Is Nothing Then
If Target.Column = 4 Then
Set rFound = Sheets("TEMP").Cells.Find(What:=Target.Value, After:=Sheets("TEMP").Range("D1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
Target.Offset(0, 17).Value = rFound.Offset(0, 17).Value
End If

End If


Sheet1.Protect


If Sheet1.Protection.AllowInsertingRows = False Then
Sheet1.Protect AllowInsertingRows:=True
End If

End Sub


[/vba]

[tools]

The event gets triggered and it shows the target column number as 1

Is there a way where i can check the columns in which data has been entered

[Bob Phillips]

Do you mean if more than one cell changed at a time?

[Simon Lloyd]

Perhaps at the following lines in your code (which by the way just changes a single value!),
[VBA]
.......Target.Offset(0, 17).Value = rFound.Offset(0, 17).Value
MsgBox Target.Offset(0, 17).Address
End If

[/VBA]

[Simon Lloyd]

I don't think we have been supplied the whole code Bob there is a Dim which doesnt reference anything!

[tools]

Yes if more than one cell has been changed then this code does not work.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rFound As Range
Dim rowlast
Dim cell As Range

Sheet1.Unprotect

On Error Resume Next
If Not Intersect(Target, Range("D4,U4,C10:C100,E10:E100,G10:G100")) Is Nothing Then

For Each cell In Target

If cell.Column = 4 Then

Set rFound = Sheets("TEMP").Cells.Find(What:=cell.Value, _
After:=Sheets("TEMP").Range("D1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
cell.Offset(0, 17).Value = rFound.Offset(0, 17).Value
End If
Next cell
End If

Sheet1.Protect

If Sheet1.Protection.AllowInsertingRows = False Then
Sheet1.Protect AllowInsertingRows:=True
End If

End Sub
[/vba]

[tools]

Thanks xld