If condition to move a group of cells based on status

[Very Basic User]

I have an excel sheet that is used to collect items that need follow up on
issues. In the sheet, I have one cell that is titles "Status" What I would
like to have happen is... IF I mark status as complete, the entire row of
information is removed form the "In progress" tab and put into the "Complete"
tab of the worksheet. It would have to cut, paste, and delete the old row.

Any help would be appreciated!

 

[Otto Moehrbach]

Right-click on the sheet "In Progress" tab, select View Code and paste this
macro into that module. "X" out of the module to return to your sheet. As
written, this macro reacts if the word "Complete" is entered into any cell
in Column C (column number 3). In that case, the first 10 columns of that
row are copied to the first blank row of the "Complete" sheet. The original
row in the "In Progress" sheet is then deleted. Come back if you need more.
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 3 And Target.Value = "Complete" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

 

[Very Basic User]

Would I replace your word "Value" with tha actual column # and or cell?
--
Thank you for your time!
John

Right-click on the sheet "In Progress" tab, select View Code and paste this
macro into that module. "X" out of the module to return to your sheet. As
written, this macro reacts if the word "Complete" is entered into any cell
in Column C (column number 3). In that case, the first 10 columns of that
row are copied to the first blank row of the "Complete" sheet. The original
row in the "In Progress" sheet is then deleted. Come back if you need more.
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 3 And Target.Value = "Complete" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

 

[Very Basic User]

Let me give you more detail too to help.

Worksheet "In Progress"
Column
A=Date, B=Shift, C=Short Description, D=Owner, E=Trigger, F=Date Expected,
G=Status, H=Root cause, prevention, timing

First row on data = row 5 through ...

Worksheet "Complete" is formated same as "In Progress" but used to hold all
Status=Complete or Canceled

File name = Copy of RCA Trigger follow up.xlsx

I hope this helps to customize the needed code. I tried to copy and paste
yours and it didn't do anything.

Thanks again!

Thank you for your time!
John

Right-click on the sheet "In Progress" tab, select View Code and paste this
macro into that module. "X" out of the module to return to your sheet. As
written, this macro reacts if the word "Complete" is entered into any cell
in Column C (column number 3). In that case, the first 10 columns of that
row are copied to the first blank row of the "Complete" sheet. The original
row in the "In Progress" sheet is then deleted. Come back if you need more.
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 3 And Target.Value = "Complete" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 10)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

 

[Otto Moehrbach]

In the phrase:
If Target.Column = 3
you would need to change the "3" to whatever column number you want. Otto

Would I replace your word "Value" with tha actual column # and or cell?

 

[Otto Moehrbach]

John
I modified the code to work on Column G (column #7) and only in rows
greater than 4, and to copy 8 columns. One problem that you may run into is
a possible typo in what you enter in Column G. The code is looking for
"Complete". You can avoid this problem by using Data Validation in the
cells of Column G to force the correct entry.
If you wish, send me an email and I'll send you the small file I used for
this. It will have the code properly placed. My address is
(e-mail address removed). Remove the "extra" from this address. HTH
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row > 4 And _
Target.Value = "Complete" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Copy Dest
Target.EntireRow.Delete
End If
End Sub

Let me give you more detail too to help.

Worksheet "In Progress"
Column
A=Date, B=Shift, C=Short Description, D=Owner, E=Trigger, F=Date Expected,
G=Status, H=Root cause, prevention, timing

First row on data = row 5 through ...

Worksheet "Complete" is formated same as "In Progress" but used to hold
all
Status=Complete or Canceled

File name = Copy of RCA Trigger follow up.xlsx

I hope this helps to customize the needed code. I tried to copy and paste
yours and it didn't do anything.

Thanks again!

Thank you for your time!
John

 

[Very Basic User]

Very helpful, worked like a charm! Thanks!

 

[Very Basic User]

Hello Otto...
The code attached below works perfectly for "Complete" items. What if I wan
it to work for both "Complete" and "Cancelled" how do I alter the code?
Thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dest As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Column = 7 And _
Target.Row > 4 And _
Target.Value = "Complete" Then
With Sheets("Complete")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
Range(Cells(Target.Row, 1), Cells(Target.Row, 8)).Copy Dest
Target.EntireRow.Delete
End If
End Sub