VBA code to move row from one sheet to another and vice versa

[Vsat]

Dear All, I am facing a challenge in writing a VBA Code, Can you please help. My Scenario is I have an Excel file with 2 sheet (ORIGINAL & COMPLETED) and i have a column called status in a drop down box. If i change the status to Completed, Status changed row should be moved to COMPLETED Sheet and if i change the Status again in COMPLETED sheet to Reopened, it should copy and go back to ORIGINAL sheet. I am able to move from ORIGINAL to COMPLETED. But i am unable to move from COMPLETED TO ORIGINAL based on the status change in COMPLETED Sheet.

My Code given below for reference

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsUse As Worksheet
Dim wsDc As Worksheet
Dim wsUse1 As Worksheet
Dim wsDc1 As Worksheet

Dim strdc As String
Dim strdc1 As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Target.Cells.Count > 1 Then Exit Sub

If Target.Column <> 5 Then Exit Sub ' change 2 to the correct column number

Set wsUse = ThisWorkbook.Sheets("ORIGINAL")
Set wsDc = ThisWorkbook.Sheets("COMPLETED")

With wsUse
strdc = Target.Value

If strdc = "Completed" Then

n = .Rows.Count
Target.Offset(0, 5).Value = Format(Now, "DD-MM-YYYY HH:mm")
Target.EntireRow.Copy
wsDc.Range("A" & n).End(xlUp).Offset(1, 0).PasteSpecial xlValues
wsDc.Range("A" & n).End(xlUp).EntireRow.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Target.EntireRow.Delete
End If

End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Fluff]

If you have a change event on both those sheets then you will need to disable events, like

        Application.EnableEvents = False
        wsDc1.Range("A" & k).End(xlUp).Offset(1, 0).PasteSpecial xlValues
        wsDc1.Range("A" & k).End(xlUp).EntireRow.PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        Application.EnableEvents = True

You will need to add the lines in red to both change events.

 

[Joe4]

If what Fluff recommended does not work (similar to what I mentioned back in post 4), please walk me through an example that causes the error. Tell me exactly what you are doing so I can try on the copy of your workbook I just downloaded.

 

[Vsat]

If what Fluff recommended does not work (similar to what I mentioned back in post 4), please walk me through an example that causes the error. Tell me exactly what you are doing so I can try on the copy of your workbook I just downloaded.

I am trying to achieve the below scenario.

When i change my status as Completed in the ORIGINAL Sheet, Data in that row should be deleted and move to the COMPLETED Sheet.
In the same way, When i change the Status from Completed as Reopened in COMPLETED sheet, it should copy the row and move to the ORIGINAL Sheet . In the COMPLETED sheet data should not be deleted. Hope you got it. Thanks


Sheet Name are written in CAPITAL LETTERS / BOLD /UNDERLINED & Highlighted for reference

 

[Fluff]

Did you try what I suggested?

 

[Vsat]

Did you try what I suggested?

Yes, I tried and now it is working,. Thank you so much Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.