Forum Discussion
Move row to new sheet auto matically when updated as complete
I have a Project Information Schedule with projects listed in column C rows 3 to 150 with information for each project in the same row.
I have a column that gets manually updated from 0% to 100%. Once it is 100% it indicates as Project completed.
I want the sheet to automatically move the entire row of the completed project to a sheet in the same work book called Completed Projects and remove the row from the sheet with the projects in progrees sheet.
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.
11 Replies
To do what you describe is either a manual process (Ctrl/X followed by Ctrl/V) or requires imperative style code (you have been provided with an Excel VBA solution, driven by the worksheet change event).
The closest that you can get with worksheet formula is to set up sheets for on-going projects and another for completed projects. Functional programming approaches will always leave the user input unaltered, but the formulas on the new sheets can create filtered copies.
= FILTER(ProgressTbl, ProgressTbl[%Completed]=1)Hi Peter,
I need your help again.
1st, how do I start a new subscription or thread to ask a new question about excel formulas and VB codes?
2nd,
On the same sheet that you gave me the VB code to insert a new row and move a line to new sheet when completed previously, I now need to open a new sheet for each of the new projects in the same workbook and for the projects already on the main sheet.
The new sheet must be labeled to the name of the new project added.
Each new sheet must also have 2 specific headings from the main sheet headings i.e,
D6=Site Name and T6=Notes3rd,
Column A from Row 3 onwards, needs to automatically insert the date once new notes are done in column B from row 3 onwards.4th,
each new sheet needs to be linked to its project on the main sheet so that when anyone clicks in the cell of the project under the heading "Notes" (cell T6) it must take them to that specific projects notes.🙂 A lot of things I want this sheet to do, haha
Regards
Gerdus- Thank you very much.
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 listedTo 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.
Since you did not provide any detail on which is the column where you change the % completed status of a project and which range is to be copied from Progress Sheet to Completed Sheet, I am assuming that the layout is similar to the attached where column M is the % Completed column which is manually filled. The Progress Sheet Module in the attached contains the following Change_Event Code so once you change the %Completed in column M for any project in column C to 100%, that row will be copied to the Completed Sheet and will be deleted from the Progress Sheet.
Please modify the code below as per your requirement.
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:L to the Completed Sheet starting from column A wsProgress.Range("A" & r & ":L" & r).Copy wsCompleted.Range("A" & dlr) wsProgress.Rows(r).Delete End If End If Skip: Application.EnableEvents = True End Sub
