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

Hopefuly this quick answer will do.
This will find the next available row :-
'---------------------------------------------------------------------------
NextRow = Worksheets("MySheet").Range("A65536").End(xlUp).Ro w +1
'---------------------------------------------------------------------------
This uses column A - make sure you use a column that you know will hav
something in the bottom cell

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

Just add these two lines to your code. The rows will be
copied to the next empty row every time. I'm assuming the
first column of the row has some data in it, if not change
(erow,1) to (erow,n) n being the column number which
always has data in it because the code checks for only
that to figure if a row is empty.

....
iRow1 = 2
erow=1
While ws2.cells(erow,1)<"":erow=erow+1:Wend
iRow2 = erow
'copy from sheet1 to sheet2
Do Until ws1.Cells(iRow1, 18) = "--1"
....

Regards

-----Original Message-----
Hi all,

I am trying to copy an entire row to a new worksheet
based on the
contents of a cell in that row.
If the cell in col 19 equals "Yes" then the row is copied
to the new
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 if
I re-run the macro, then the new rows being copied
accross overwrite
the old ones. I need to be able to select the next blank
row on the new
sheet (sheet1).
The background to this is that once a job on the 'Shut
Plan' is
marked as 'Yes' then the job is done and the row can now
be archived on
'sheet1' by running a macro. This macro also deletes the
row off the
'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
Trefor


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

.

Thanks Brian,
Still cant seem to get it to work!!! Everytime I re-run the macro i
starts from row 2 in sheet1 and overwrites existing data. Is there
specific place that I should include this code?
Trefo

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

Thanks Serkan,
Thats done the trick !!!!
Thanks very much !!!
Trefo

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

hey cakse

is it possible for me to see this worksheet of yours? I have the same problem... trying to solve it for a month now but no luck... My problem is exactly like yours though.. so any help would be great.. an example would rock

Jax Baxter

Jax,
I cant sent you a copy of the file because its contains personne
lists, machine information etc. Releasing this information is agains
company policy, and will get me sacked !!! However I will remove th
data and send it you but it may take some time (probably tomorrow!)
Trefo

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

hehe, i understand exactly what you mean. Its the same thing with my company policy

If you could remove the data and perhaps put in some fake data or something.. It would come in very handy.. I've been trying to get something working here for about a month now. Tomorrow is fine with me =

Thanks a lot for replying,
Jay Baxte

My email is