Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Delete Empty Rows Macro | Excel Programming | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
Can Excel "slide up" rows with content thru empty rows to condense | Excel Worksheet Functions |