U
udi Hen
Hi! I have a workbook with two worksheets in it. The first "OPEN" and
the second "CLOSED". In "OPEN", I have rows of tasks starting in row 4
(row 3 is my header), going to 39. What I'd like to have happen is when
all cells are full then a value "Closed" appear in column I,and it moves
the entire row to the "Completed" worksheet,and removes it from the
"OPEN" worksheet. I have a macro that know how to cut the completed rows
and deleted them but I don't know how to move up the open rows.
Also,If I move rows up,then I shorten the table.
I want that the open tasks (that has "OPEN" value in column "i") will
move up in sheet "OPEN" and the number of rows that were cut into sheet
"CLOSED" will be added to the end of table (I want that the table has
the same number of rows all the time).
Here is my macro for now:
Thanks,Udi
Sub moveclosedrows()
Dim X As Long, LastRow As Long
Application.ScreenUpdating = False
For X = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ActiveSheet.Cells(X, "I").Value = "CLOSED" Then
LastRow = Sheets("CLOSED").Cells(Rows.Count,
"B").End(xlUp).Offset(1).Row
ActiveSheet.Cells(X, "A").EntireRow.Cut
Destination:=Sheets("CLOSED").Range("A" & LastRow)
ActiveSheet.Cells(X, "A").EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
*** Sent via Developersdex http://www.developersdex.com ***
the second "CLOSED". In "OPEN", I have rows of tasks starting in row 4
(row 3 is my header), going to 39. What I'd like to have happen is when
all cells are full then a value "Closed" appear in column I,and it moves
the entire row to the "Completed" worksheet,and removes it from the
"OPEN" worksheet. I have a macro that know how to cut the completed rows
and deleted them but I don't know how to move up the open rows.
Also,If I move rows up,then I shorten the table.
I want that the open tasks (that has "OPEN" value in column "i") will
move up in sheet "OPEN" and the number of rows that were cut into sheet
"CLOSED" will be added to the end of table (I want that the table has
the same number of rows all the time).
Here is my macro for now:
Thanks,Udi
Sub moveclosedrows()
Dim X As Long, LastRow As Long
Application.ScreenUpdating = False
For X = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
If ActiveSheet.Cells(X, "I").Value = "CLOSED" Then
LastRow = Sheets("CLOSED").Cells(Rows.Count,
"B").End(xlUp).Offset(1).Row
ActiveSheet.Cells(X, "A").EntireRow.Cut
Destination:=Sheets("CLOSED").Range("A" & LastRow)
ActiveSheet.Cells(X, "A").EntireRow.Delete
End If
Next
Application.ScreenUpdating = True
End Sub
*** Sent via Developersdex http://www.developersdex.com ***