May 14 2022 12:03 AM
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.
May 14 2022 01:53 AM
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
May 14 2022 03:44 AM - edited May 14 2022 03:57 AM
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)
May 15 2022 11:14 PM
May 16 2022 12:45 AM
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.
May 16 2022 02:37 AM
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.
May 16 2022 10:39 PM
May 17 2022 04:42 AM
Solution
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.
May 18 2022 10:11 PM
May 19 2022 12:15 AM
You're welcome @Gerdus4! Glad I could help.
May 19 2022 05:28 AM
Jun 07 2022 12:16 AM
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
May 17 2022 04:42 AM
Solution
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.