Hi, I'm using Windows 10 Pro and Excel 2010 pro and the following VBA code shown below in four of the sheets in my workbook. It had been working great since i created the Workbook several months ago.... i.e. when a date is entered in a cell within the range named "rngTrigger", the entire row of the particular cell where the date is entered is automatically cut and pasted into a different worksheet named"PSWS Closed Out Plans".
However, now, whenever a date is entered in one of the "rngTrigger" cells, the VBA error listed in the title above appears and Excel proceeds to freeze up and needs to be manually shut down using task manager.
There are three other team members of mine who also use this workbook and Excel 2010 pro and they are experiencing the same error/problem. The Workbook is protected and shared however NONE of the worksheets are protected. I tried unprotecting the workbook to see if this was the issue however continued to encounter the same error afterwards.
Can anyone please explain to me exactly what i need to change in the code below to fix the problem and stop the error from occurring?
Please note: I did not create but found this code with the help of Google and am a novice at best when it comes to using Macros/VBA.
However, now, whenever a date is entered in one of the "rngTrigger" cells, the VBA error listed in the title above appears and Excel proceeds to freeze up and needs to be manually shut down using task manager.
There are three other team members of mine who also use this workbook and Excel 2010 pro and they are experiencing the same error/problem. The Workbook is protected and shared however NONE of the worksheets are protected. I tried unprotecting the workbook to see if this was the issue however continued to encounter the same error afterwards.
Can anyone please explain to me exactly what i need to change in the code below to fix the problem and stop the error from occurring?
Please note: I did not create but found this code with the help of Google and am a novice at best when it comes to using Macros/VBA.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
Set rngDest = Worksheets("PSWS Closed Out Plans").Range("rngDest")
' Limit the trap area to range of cells in which completed dates are entered as defined above
If Not Intersect(Target, Range("rngTrigger")) Is Nothing Then
' Only trigger if the value entered is a date or is recognizable as a valid date
If IsDate(Target) Then
'Ensure subsequent deletion of 'moved' row does NOT cause the Change Event to run again and get itself in a loop!
Application.EnableEvents = False
Target.EntireRow.Select
Selection.Cut
rngDest.Insert Shift:=xlDown
Selection.Delete
' Reset EnableEvents
Application.EnableEvents = True
End If
End If
End Sub
