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

Vsat

New Member
Joined
Aug 27, 2020
Messages
13
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,830
Office Version
  1. 365
Platform
  1. Windows
If you have a change event on both those sheets then you will need to disable events, like
Rich (BB code):
        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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,443
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 27, 2020
Messages
13
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,830
Office Version
  1. 365
Platform
  1. Windows
Did you try what I suggested?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,830
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,709
Messages
5,573,735
Members
412,550
Latest member
soking
Top