ok, try this:
put this in the sheet code for tab "Destruction"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("L4:L338")
If Not Application.Intersect(KeyCells, Target) Is Nothing Then
processcells Application.Intersect(KeyCells, Target)
End If
End Sub
note the upper range of keycells
and put this in a module:
Sub processcells(ByVal Target As Range)
Dim ws As Worksheet
Dim cell As Range
Dim lr As Long
Set ws = Sheets("Completed Destruction")
For Each cell In Target.Cells
Select Case cell.Value
Case cell.Value
lr = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row + 1
If cell.Value = "Y" Then
Range("A" & Target.Row & ":M" & Target.Row).Copy
Sheets("Completed Destruction").Range("A" & lr).PasteSpecial xlPasteValues
Target.EntireRow.Delete
End If
End Select
Next cell
End Sub
probably don't need the case statement, it's a holdover from my older code.
Bookmarks