Hi all,

I am trying to copy an entire row to a new worksheet based on th
contents of a cell in that row.
If the cell in col 19 equals "Yes" then the row is copied to the ne
sheet (sheet1) and then deleted from the original sheet (Shut Plan)
The code I have got does this function well, but my problem is that i
I re-run the macro, then the new rows being copied accross overwrit
the old ones. I need to be able to select the next blank row on the ne
sheet (sheet1).
The background to this is that once a job on the 'Shut Plan' i
marked as 'Yes' then the job is done and the row can now be archived o
'sheet1' by running a macro. This macro also deletes the row off th
'Shut Plan' as it is now archived on 'sheet1'.
The code I have got so far (which I found on this forum !) is :

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

Set ws1 = Sheets("Shut Plan")
Set ws2 = Sheets("Sheet1")
iRow1 = 2
iRow2 = 2
'copy from sheet1 to sheet2
Do Until ws1.Cells(iRow1, 18) = "--1"
If ws1.Cells(iRow1, 19) = "Yes" 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, 19) = "Yes" Then ws1.Rows(iCt).Delete
Next iCt

Any help with this will be much appreciated,
Many thanks
Trefo

--
Message posted from http://www.ExcelForum.com