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]

You said in post 3:
When the status (Col G) changes to "Completed"

In post 8 you said:
Can I please move three rows when the status changes to "Closed",

So I'm getting confused here.

Is it closed or completed

 

[nmrkumar]

Sorry for not being clear. I will stick to "Closed". Thank you.

 

[MaxxLevell]

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

Good Morning...new member on this board, and joined because I found this post via a google search to an issue I am trying to resolve. Obviously, I am brand new to any sort of vba techniques, and am looking forward to learning more. I had a similar question about moving rows from one sheet to another if a criteria is met, and modified the above code, and it is working perfectly for what I initially wanted to do. I simply added a column to my original spreadsheet with a column called "Completed", and will enter a 'Y' or 'N'. I added the code to look for the 'Y', and am successfully moving those rows to a similarly named sheet in the workbook. However, I'd also like to move the rows with 'N' to another sheet as well. Can I get a hint as to how to go about doing that? I'm not sure if I just need to modify the original code, add another module, etc.

Thanks for any input and assistance...

 

[My Aswer Is This]

Good Morning...new member on this board, and joined because I found this post via a google search to an issue I am trying to resolve. Obviously, I am brand new to any sort of vba techniques, and am looking forward to learning more. I had a similar question about moving rows from one sheet to another if a criteria is met, and modified the above code, and it is working perfectly for what I initially wanted to do. I simply added a column to my original spreadsheet with a column called "Completed", and will enter a 'Y' or 'N'. I added the code to look for the 'Y', and am successfully moving those rows to a similarly named sheet in the workbook. However, I'd also like to move the rows with 'N' to another sheet as well. Can I get a hint as to how to go about doing that? I'm not sure if I just need to modify the original code, add another module, etc.

Thanks for any input and assistance...

Glad to see your a new member to the forum.
It's always best to post a new question to the forum and i will see it and try to help you.
And do not say move the row to another sheet. Please say move row to sheet named "Alpha" or such. And when you say move do you mean copy row to sheet named "Alpha" and delete from original sheet. Please be very specific. And do not say column Named Completed it's best to say column "G" or such. Again be specific. Thanks and I look forward to seeing your new posting