Automatically move a row to another sheet based on cell value

[mossi8]

Hi there.

Complete VBA and Macro novice here and I have trawled the internet trying to find a solution.

I have a basic spreadsheet with 2 sheets, titled 'Active' and 'Complete'.

In Sheet 1 (Active) I have data in columns A - I. Column I shows a status as either Active or Complete.

I want my spreadsheet to automatically cut and paste 'complete' rows into sheet 2 (Complete). Deleting the data from the original sheet.

Ideally, I would like this to happen automatically when a row is marked as 'Complete' and not have to have a button to click each time I want to update.

Please help!

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named Active
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/20/2019  4:18:03 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 9 Then
Dim Lastrow As Long
Lastrow = Sheets("Completed").Cells(Rows.Count, 9).End(xlUp).Row + 1
Dim ans As Long
ans = Target.Row
If Target.Value = "Completed" Then
    Rows(ans).Copy Sheets("Completed").Rows(Lastrow)
    Rows(ans).Delete
End If

End If
End Sub

 

[nmrkumar]

Hi,
I am trying to do the similar think but it is not working. Please help. I don't have lot of experience and I really appreciate your help. I am have attached the excel sheet that I am working. When the status (Col G) changes to "Completed" I want the complete row in sheet "Open" cut & Paste to another sheet "Close". Please help.

 

[nmrkumar]

 

[My Aswer Is This]

In you post your image shows "Closed" but you said "Completed"
My script looks for the word completed.
Modify the script is needed.
Try this
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab with your current data
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you enter "Completed" in column G see at the top of my script I used 7 which means column 7 the script will copy the row to sheet named Close
And delete the row from the original sheet.
If you do not want the row deleted just remove that line of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 2/22/2020 11:42:10 AM EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 7 Then
Dim Lastrow As Long
Lastrow = Sheets("Close").Cells(Rows.Count, 7).End(xlUp).Row + 1
Dim ans As Long
ans = Target.Row
If Target.Value = "Completed" Then
Rows(ans).Copy Sheets("Close").Rows(Lastrow)
Rows(ans).Delete
End If
End If
End Sub

 

[nmrkumar]

Thank you so much for your help. You are excellent. It worked but here is he challenge, I have merged cell and it is transferring only one row instead of 3 rows. Please help. Thank you.

 

[My Aswer Is This]

I know of no way to do that. It is mentioned on this forum often that using merged cells is not advisable and can be difficult work when using Vba

 

[nmrkumar]

Thank you. I removed the merge cell and replace with three row. Can I please move three rows when the status changes to "Closed",. For example, per the attached picture, row# 11, 12 ,13 should move to other sheet . Please kindly help.

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/23/2020  10:08:41 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 7 Then
Dim Lastrow As Long
Lastrow = Sheets("Close").Cells(Rows.Count, 7).End(xlUp).Row + 1
Dim ans As Long
ans = Target.Row
If Target.Value = "Completed" Then
Rows(ans).Resize(3).Copy Sheets("Close").Rows(Lastrow)
Rows(ans).Resize(3).Delete
End If
End If
End Sub

 

[nmrkumar]

Thank you. It is still only moving 1 row. Please help.