MACRO THAT CUT ROWS BETWEEN SHEETS AND ADD EMPTY ROWS
 

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 ***