Help debugging

[mat3572]

With this code here its supposed to copy the range of the cell I currently select delete it from the sheet and paste it to another sheet but I keep getting a 1004 run time error. I put ** next to the line I keep getting errors on.

'Remove Case Sensitivity
Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cell As Range
Dim rng2 As Range
Dim pasteSheet As Worksheet
'Set pasteSheet = Worksheets("Print - Closed")
Set rng = Range("P5:P200")
'Determine if change was made to any cell in col O
If Not Intersect(Target, rng) Is Nothing Then
For Each cell In rng
'Determine if the work "yes" is contained within a cell
If InStr(1, cell, "Yes") > 0 Then

**Set rng2 = Range(ActiveCell.Offset(-1, -14), ActiveCell.Offset(-1, 0))
rng2.Select
Selection.Copy
If InStr(1, Selection.Columns("B"), "Cut") > 0 Then
Set pasteSheet = Worksheets("Cut - Closed")
ElseIf InStr(1, Selection.Columns("B"), "Print") > 0 Then
Set pasteSheet = Worksheets("Print - Closed")
ElseIf InStr(1, Selection.Columns("B"), "Pack") > 0 Then
Set pasteSheet = Worksheets("Pack - Closed")
ElseIf InStr(1, Selection.Columns("B"), "Secondary") > 0 Then
Set pasteSheet = Worksheets("Secondary Process - Closed")
Else
Set pasteSheet = Worksheets("Closed")


End If
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Selection.EntireRow.Delete shift:=xlUp
'Worksheets("Main").Range("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete shift:=xlUp
Worksheets("MAIN").Range("P5:P200").ClearContents
Application.CutCopyMode = False
End If
Next cell
End If

End Sub

 

[mat3572]

For example, when I type Yes in column 11 it takes the row and posts it in another sheet like so...

 

[Joe4]

OK, a few things to consider:

1. Worksheet_Change event procedures are triggered by non-formulaic updates to cells on that sheet.
If your code itself makes any changes to the sheet, it will call/trigger itself (if not careful, you can get caught in an endless loop).
So it is often necessary to "disable" the code from calling itself while making changes.
That can be done by adding this line of code before the code makes changes to that sheet:

Application.EnableEvents = False

but you then need to be sure to turn it back on, or other changes won't trigger the code.
So you then add this line in after the code is done making changes to that sheet:

Application.EnableEvents = True

2. If you delete a row, you are in essence updating 16384 cells (the number of columns in a single row).
That can send Worksheet_Change event procedure code into a tizzy!
So, if you may be manually deleting rows that you DON'T want to trigger any code, you want to gracefully exit the code.
You can do that by adding a line like this at the top of the code:

If Target.CountLarge > 1 Then Exit Sub

That basically says that if more than one cell is updated at once, exit the procedure without doing anything.

These are the two most common things you need to account for in Worksheet_Change event procedure code.