I have a scheduling workbook with one sheet that contains information of active jobs and another sheet that is to be used as a job archive. the active job sheet as 2 columns with check boxes for if the job is complete, and if the job has been fully invoiced. Once both of these columns are marked "yes", I want the entire row to be cut from the active job sheet and pasted to the archived job sheet. I have the following macro that seems to work to move the rows from the active sheet to the archive sheet:
Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If Flag = True Then Exit Sub
If Not Intersect(Target, Range("I2:I" & LR)) Is Nothing Then
If Target.Value = "archive" Then
LR = Sheets("job sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
Sheets("job archive").Range("A" & LR).PasteSpecial
Flag = True
Target.EntireRow.Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub
However, I need to be able to have a similar macro on the archive job sheet in case an old job comes back for repair or rework, I need to be able to move the entire row back to the active job sheet. I have tried the same marco on both sheets and get errors when trying to move between both sheets.
Please help.
Option Explicit
Dim Flag As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
If Flag = True Then Exit Sub
If Not Intersect(Target, Range("I2:I" & LR)) Is Nothing Then
If Target.Value = "archive" Then
LR = Sheets("job sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
Target.EntireRow.Copy
Sheets("job archive").Range("A" & LR).PasteSpecial
Flag = True
Target.EntireRow.Delete
End If
End If
Application.CutCopyMode = False
Flag = False
End Sub
However, I need to be able to have a similar macro on the archive job sheet in case an old job comes back for repair or rework, I need to be able to move the entire row back to the active job sheet. I have tried the same marco on both sheets and get errors when trying to move between both sheets.
Please help.