VBA macro for moving entire row between worksheets

sminsaas

New Member
Joined
Mar 10, 2016
Messages
2
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,215,443
Messages
6,124,890
Members
449,194
Latest member
JayEggleton

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top