try this:
Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, Mini As Integer, Maxi As Integer
LR = Range("A" & Rows.Count).End(xlUp).Row
Mini = Int(Target.Row / 7) * 7 + 2 'starting row to copy
Maxi = Mini + 6 'ending row to copy
If Flag = True Then Exit Sub
If Not Intersect(Target, Range("K2:K" & LR)) Is Nothing Then
If Cells(Mini + 1, 11).Text = "Complete" _
And Cells(Mini + 2, 11).Text = "Complete" _
And Cells(Mini + 3, 11).Text = "Complete" _
And Cells(Mini + 4, 11).Text = "Complete" Then
LR = Sheets("Completed_Projects").Range("B" & Rows.Count).End(xlUp).Row + 3
Sheets("Projects").Range("A" & Mini & ":N" & Maxi).Copy
Sheets("Completed_Projects").Activate
Sheets("Completed_Projects").Range("A" & LR).PasteSpecial
Flag = True
Sheets("Projects").Range("A" & Mini & ":N" & Maxi).Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub
Bookmarks