Trying to get change event to fire a 2nd time

[vincent2722]

Hello,

I have a worksheet that I frequently paste data into so that it can be used by another sheet in the workbook. I've inserted a procedure to change numbers stored as text into values into the worksheet object, and it works perfectly when I paste the data into the sheet. However, if I delete the entered data and try to paste more data again to replace the original, the change event does not fire.

I can, of course, open up the VBE and stop the procedure and try pasting again and it works fine. My question is if there is some way to get the event to fire again in the same session when I paste more data.

Thanks for your help in advance!

Vince

 

[Mike Blackman]

Hi and welcome to the board,

Can we see your code? I can't think of a reason why the event wouldn't trigger a second time unless your switching off EnableEvents and not switching it back on again.

 

[vincent2722]

Thanks for the response, Mike!

Here's what I've got:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Not Intersect(Target, Columns(1)) Is Nothing Then
Application.EnableEvents = False
Cells.SpecialCells(xlCellTypeLastCell) _
.Offset(1, 1).Copy
Columns(1).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
With Columns(1)
.VerticalAlignment = xlTop
.WrapText = False
End With
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Mike Blackman]

Hmmm, I really can't see why it wouldn't trigger a second time assuming your pasting into Column A...