Forum Discussion
Move row to new sheet auto matically when updated as complete
Please find the two codes below, one for Progress Sheet Module and another for Completed Sheet Module. I have tweaked the Progress Sheet Module code a bit so not it will also copy the %Completed column to the Completed Sheet i.e. now it will copy the range A:M instead of A:L.
You will have to follow the same steps I mentioned in my last post to place the codes on both the Sheet Modules.
Progress Sheet Module Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim wsProgress As Worksheet Dim wsCompleted As Worksheet Dim dlr As Long Dim r As Long Set wsProgress = Worksheets("Progress") Set wsCompleted = Worksheets("Completed") On Error GoTo Skip: If Target.Column = 13 And Target.Row > 2 Then '13 here means Column M which is %Completed Column on Progress Sheet. Change it as per your need Application.EnableEvents = False If Target.Value = 1 Then r = Target.Row dlr = wsCompleted.Cells(Rows.Count, "A").End(xlUp).Row + 1 'Following line will coply the range A:M to the Completed Sheet at the bottom wsProgress.Range("A" & r & ":M" & r).Copy wsCompleted.Range("A" & dlr) wsProgress.Rows(r).Delete End If End If Skip: Application.EnableEvents = True End SubCompleted Sheet Module Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim wsProgress As Worksheet Dim wsCompleted As Worksheet Dim dlr As Long Dim r As Long Set wsProgress = Worksheets("Progress") Set wsCompleted = Worksheets("Completed") On Error GoTo Skip: If Target.Column = 13 And Target.Row > 2 Then '13 here means Column M which is %Completed Column on Progress Sheet. Change it as per your need Application.EnableEvents = False If IsNumeric(Target.Value) And Target.Value < 1 Then r = Target.Row dlr = wsProgress.Cells(Rows.Count, "A").End(xlUp).Row + 1 'Following line will copy the range A:M to the Progress Sheet at the bottom wsCompleted.Range("A" & r & ":M" & r).Copy wsProgress.Range("A" & dlr) wsCompleted.Rows(r).Delete End If End If Skip: Application.EnableEvents = True End SubPlease find the attached with both the codes in place.
when I download the sheet you provided the codes work, but when I transfer the code to my sheet it does not activate the code.
I have saved my worksbook as macros enabled.
when i press the play button the window opens to choose the macros but it is not listed
To implement the code to your actual workbook, please follow these steps...
1. Right click on Sheet Tab Name and choose View Code, this will open the VB Editor Code Window.
2. Copy the code from the above post, and paste it into the opened code window.
3. Save your workbook as a Macro-Enabled workbook.
The code is a Change Event code and it will run automatically once input the %Completed value in the target column. As per the existing code, the code will be triggered automatically if you input the value in column 13 i.e. column M. Please change it in the code as per your need.
- Thank you very much.
I need another favour.
Some times I need to re-open a project that has been completed due to new work.
I will do this by changing the percenage complete from 100% to anything below 100%
Once I have done the change I want the project to automatically goback to the In Progress sheet.
it can add it back at the bottom.
RegardsPlease find the two codes below, one for Progress Sheet Module and another for Completed Sheet Module. I have tweaked the Progress Sheet Module code a bit so not it will also copy the %Completed column to the Completed Sheet i.e. now it will copy the range A:M instead of A:L.
You will have to follow the same steps I mentioned in my last post to place the codes on both the Sheet Modules.
Progress Sheet Module Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim wsProgress As Worksheet Dim wsCompleted As Worksheet Dim dlr As Long Dim r As Long Set wsProgress = Worksheets("Progress") Set wsCompleted = Worksheets("Completed") On Error GoTo Skip: If Target.Column = 13 And Target.Row > 2 Then '13 here means Column M which is %Completed Column on Progress Sheet. Change it as per your need Application.EnableEvents = False If Target.Value = 1 Then r = Target.Row dlr = wsCompleted.Cells(Rows.Count, "A").End(xlUp).Row + 1 'Following line will coply the range A:M to the Completed Sheet at the bottom wsProgress.Range("A" & r & ":M" & r).Copy wsCompleted.Range("A" & dlr) wsProgress.Rows(r).Delete End If End If Skip: Application.EnableEvents = True End SubCompleted Sheet Module Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub Dim wsProgress As Worksheet Dim wsCompleted As Worksheet Dim dlr As Long Dim r As Long Set wsProgress = Worksheets("Progress") Set wsCompleted = Worksheets("Completed") On Error GoTo Skip: If Target.Column = 13 And Target.Row > 2 Then '13 here means Column M which is %Completed Column on Progress Sheet. Change it as per your need Application.EnableEvents = False If IsNumeric(Target.Value) And Target.Value < 1 Then r = Target.Row dlr = wsProgress.Cells(Rows.Count, "A").End(xlUp).Row + 1 'Following line will copy the range A:M to the Progress Sheet at the bottom wsCompleted.Range("A" & r & ":M" & r).Copy wsProgress.Range("A" & dlr) wsCompleted.Rows(r).Delete End If End If Skip: Application.EnableEvents = True End SubPlease find the attached with both the codes in place.
- thank you very much, much appreciated.
quick and easy.
the best assistance I got on this