| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting based on text status of 3 cells | Excel Discussion (Misc queries) | |||
| Scan group of cells and result based on condition | Excel Discussion (Misc queries) | |||
| move cells based on group levels | Excel Discussion (Misc queries) | |||
| WHEN I GROUP IT ERRORS WITH 'CANNOT MOVE CELLS OFF WORKSHEET?? | Excel Worksheet Functions | |||
| format group of rows based on condition | Excel Discussion (Misc queries) | |||
Linear Mode
