Here is a summary of what I want done...any help would be greatly appreciated.. please... I've been looking on here for a while now but havent been able to produce results

This is what my workbook currently looks like

Rows 1-4 contain the Title of the workbook... Like a header block
The actual spreadsheet begins at row

Sheet
Column/Titl

C5/Warning (IF formula used to output Warning {"In Progress", "Closed", "No date", "Action Due", etc
D5/Days Left (IF formula used to output days left until target close out date {"###" (number) or "" (blank)
E5/Clien
F5/CPI No
G5/Typ
H5/Department
N5/Target Close Out Dat
P5/Days Late (IF formula used to output days passed from target close out date {"###" (number) or "" (blank)
Q5/Actioned by:
AB5/Status
AC5-AG5/Comments (Merged
There are other columns in between, some are hidden, some are not

What i want is a macro/code that loops through column AB in sheet1 and checks each cell for "Closed"
If the cell has "Closed" in it.. I want that entire row moved to sheet2

For instance, say in Sheet1.. we have
AB6 Close
AB7 Close
AB8 Ope
AB9 Close

I'd like Row 6,7 and 9 moved to Sheet2 under row 6,7 and

and If i ran the same code again on another day or time.. it would look for new Closed entires and move those rows to sheet2.... So for instance on another day i would run the code again... and under AB20 there is a Closed so it would move row 20 to sheet2 row9..

This created list on Sheet2 needs to be continuous, without any space
that may be caused by Open's or any other text other than "Closed" in column AB of sheet1

Please help if you can.. am a desperate ma

Jay Baxte

Here is a piece of code I found and edited.. It may work. I have some questions on the code though..

What does "For iCt = 1 To 21" mean? What does the variable iCt do
And how do we know what numbers to make IRow1, erow, and iCt equal to...? Am a bit lost on tha

Dim iCt As Intege
Dim iRow1 As Intege
Dim iRow2 As Intege
Dim ws1 As Workshee
Dim ws2 As Workshee

Set ws1 = Sheets("Sheet1"
Set ws2 = Sheets("Sheet2"
iRow1 =
erow=
While ws2.cells(erow,1)<"":erow=erow+1:Wen
iRow2 = ero

'copy from sheet1 to sheet
Do Until ws1.Cells(iRow1, 27) = "--1
If ws1.Cells(iRow1, 28) = "Closed" The
For iCt = 1 To 2
ws2.Cells(iRow2, iCt) = ws1.Cells(iRow1, iCt
Next iC
iRow2 = iRow2 +
End I
iRow1 = iRow1 +
Loo

'delete from sheet
For iCt = iRow1 To 2 Step -
If ws1.Cells(iCt, 28) = "Yes" Then ws1.Rows(iCt).Delet
Next iC

Hey Jay... did you happen to go to Westlake High School in
Texas?

-----Original Message-----
Here is a piece of code I found and edited.. It may
work. I have some questions on the code though...

What does "For iCt = 1 To 21" mean? What does the
variable iCt do?
And how do we know what numbers to make IRow1, erow, and
iCt equal to...? Am a bit lost on that

Dim iCt As Integer
Dim iRow1 As Integer
Dim iRow2 As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
iRow1 = 5
erow=4
While ws2.cells(erow,1)<"":erow=erow+1:Wend
iRow2 = erow

'copy from sheet1 to sheet2
Do Until ws1.Cells(iRow1, 27) = "--1"
If ws1.Cells(iRow1, 28) = "Closed" Then
For iCt = 1 To 21
ws2.Cells(iRow2, iCt) = ws1.Cells(iRow1, iCt)
Next iCt
iRow2 = iRow2 + 1
End If
iRow1 = iRow1 + 1
Loop

'delete from sheet1
For iCt = iRow1 To 2 Step -1
If ws1.Cells(iCt, 28) = "Yes" Then ws1.Rows(iCt).Delete
Next iCt

.

nope sorry mate