SOLVED

Move row to new sheet auto matically when updated as complete

Copper Contributor

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.

 

11 Replies

@Gerdus4 

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

 

 

@Gerdus4 

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)
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 listed

@Gerdus4 

The formulas I used do not require macros to be enabled as they are simply worksheet formulas.  What they do require is that you identify the source data by naming it.  I achieved that by converting the input data range to an Excel Table (Home/Styles/Format_as _Table) and renaming it from 'Table1' to 'ProgressTbl' using the Table Name box on the Table_Design ribbon tab that shows when a Table is selected.  The advantage of all this is that tables references grow as data is added so the dependent formulas always address the correct region.

 

If you were to decide that the formula approach is sufficient  then the macros would need to be disabled; otherwise they will 'steal' your input data and 'hide' it on the Completed' sheet (which is precisely what they were written to do).  The approach I would recommend is simply that which fits into your business processes the better.

@Gerdus4 

 

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.

Regards
best response confirmed by Gerdus4 (Copper Contributor)
Solution

@Gerdus4 

 

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 Sub

 

Completed 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 Sub

 

Please 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

You're welcome @Gerdus4! Glad I could help.

I have another request :)
I want to have like a plus sign to add a new row or something simmular but it needs to add the previous row's formulas, hyper links, formatting etc, without the data.
so a clean new line with formatting and formulas

@Peter Bartholomew 

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=Notes

3rd,
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

1 best response

Accepted Solutions
best response confirmed by Gerdus4 (Copper Contributor)
Solution

@Gerdus4 

 

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 Sub

 

Completed 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 Sub

 

Please find the attached with both the codes in place.

 

 

 

View solution in original post