If condition to move a group of cells based on status
 

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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

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" wrote in message
...
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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

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


"Otto Moehrbach" wrote:

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" wrote in message
...
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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

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


"Otto Moehrbach" wrote:

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" wrote in message
...
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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

In the phrase:
If Target.Column = 3
you would need to change the "3" to whatever column number you want. Otto
"Very Basic User" wrote in message
...
Would I replace your word "Value" with tha actual column # and or cell?
--
Thank you for your time!
John


"Otto Moehrbach" wrote:

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" wrote in
message
...
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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

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
. 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
"Very Basic User" wrote in message
...
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


"Otto Moehrbach" wrote:

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" wrote in
message
...
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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

Very helpful, worked like a charm! Thanks!
--
Thank you for your time!
John


"Otto Moehrbach" wrote:

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
. 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
"Very Basic User" wrote in message
...
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


"Otto Moehrbach" wrote:

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" wrote in
message
...
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!
--
Thank you for your time!
John

If condition to move a group of cells based on status
 

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
--
Thank you for your time!
John


"Otto Moehrbach" wrote:

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
. 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
"Very Basic User" wrote in message
...
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


"Otto Moehrbach" wrote:

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" wrote in
message
...
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!
--
Thank you for your time!
John