Copying Rows Between Sheets

[lemo]

hi.
i need to copy data from one worksheet to another based on certain criteria.
i wrote the following simple initial code to do the job. i now have one problem and one question:

1 - the code works fine when i run it once. if i run it the second time, without any changes to worksheets, i expect it to simply put the same data on top of what it copied already. but instead it does something bizarre - it pushes down the data it copied on the first run, ignores the 'if' statement, and copies just a few top rows of the source sheet where the previously copied data was.

2 - generally, is this code good for the task? any glaring deficiencies? i know there are a few ways to copy-paste data between sheets (and workbooks), but not being a true programmer, i really have no clue about the nuances - efficiency, 'error-proneness', etc.

thanks,
len

Sub FileCompleted()

copyrow = 3
pasterow = 3
totrows = WorksheetFunction.CountA(Sheets("ALLORDERS").Range("a:a")) - 2

For r = copyrow To totrows
    If Range("V" & r) = "Completed" Then
        Sheets("COMPLETEDv2").Range("A" & pasterow & ":Z" & pasterow).Value = _
        Sheets("ALLORDERS").Range("A" & r & ":Z" & r).Value
        pasterow = pasterow + 1
    End If
Next r

End Sub

 

[Brianwarnock]

There is nothing in the code to cause the data to be pushed down, however I wonder why your totrows includes -2, I know you are not going to copy the first 2 rows but that is taken care of by starting the copy at row 3. Do you not want the last 2 rows?

BTW I use the following to find the lastrow as it does not rely on data being in a cell

lastrow = Sheets("sheet1").UsedRange.Rows.Count

Brian

 

[lemo]

sorry for being mia, and thanks for the suggestions, Brian.. will report with new questions, but i am hoping i can handle this myself now.. it's not that hard.. or is it?...
l

 

[lemo]

this code is killing me!
i spent hours and hours today again, it still doesn't work and i have absolutely zero idea why.. help please!!!

it's almost the same, see below, but now it pastes one row, then gives me 'Application-defined or Object-defined' error, at the point where it supposed to copy.

Sub FileByStatus1()

Dim pasterowCo As Integer
Dim lastrow As Integer
Dim r As Integer

pasterowCo = WorksheetFunction.CountA(Sheets("COMPLETEDfiled").Range("a:a")) + 3
lastrow = WorksheetFunction.CountA(Sheets("ALLORDERS").Range("a:a")) + 1

For r = 4 To lastrow
    If Sheets("ALLORDERS").Range("M" & r) = "COMPLETED" Then
        Sheets("COMPLETEDfiled").Range("A" & pasterowCo & ":AA" & pasterowCo).Value = _
        Sheets("ALLORDERS").Range("A" & r & ":AA" & r).Value
        pasterowCo = pasterowCo + 1
    End If
Next r

End Sub

 

[Brianwarnock]

Well now you have changed what you are doing so that you start 3 rows down from what is already there on COMPLETEDfiled instead of overwriting.

I would point out that your previous code worked for me after the correction I mentioned.

Why are you adding 1 to lastrow, are you confusing the operation of For...Next with Do ... Until?

BTW you are not doing a copy/paste but setting values =. I presume that you don't want to copy formulae and formats.eg colours .

A copy/paste could be

Sheets("ALLORDERS").Range("A" & r).EntireRow.Copy _
Destination:=Sheets("COMPLETEDfiled").Range("A" & pasterow)

Can't tell by just looking at the code why you are getting your error, have you stepped through it?
Click in the margin just to the left of a action code row ie not a DIM
the code will stop at the now highlighted row and you can step through using F8 checking values.
reclick to remove .

Brian

 

[lemo]

thanks.

i prefer not to use
lastrow = Sheets("sheet1").UsedRange.Rows.Count
because the people who will be using this file may start entering random data here and there outside the main data range. is it a valid concern?

i add 1 to lastrow now, because (thanks for pointing it out), -2 would give me the number of rows with data (i have 2 rows for headers), not the position of last row. oversight on my part..

i want copy/pastevalues, yes.

i've never 'stepped through' the code, but will give it a shot. and i guess i should try the original code with your corrections, since it worked for you.

thanks again for all your help, i really appreciate..
l

 

[Brianwarnock]

i prefer not to use
lastrow = Sheets("sheet1").UsedRange.Rows.Count
because the people who will be using this file may start entering random data here and there outside the main data range. is it a valid concern?

l

If it is a valid possibility then neither appraoch works as they may type in Col A thus affecting your Counta.

We must assume that the row below the main body of data is left enpty, then we can use 1 of 2 methods
1 Use Currentregion
2 just test Col A

Samples of both are below, both work on my test sheet, you will of course have to modify for your sheet and column references, but then reading through the code to modify it proves that you understand it, far better than a simple copy and paste

Brian

Sub copyvalues1()
Dim fromrow As Long
Dim torow As Long
Dim lastrow As Long
Dim r As Long

fromrow = 3
lastrow = Sheets("sheet1").Range("a3").CurrentRegion.Rows.Count + 2
' + 2 because the data region starts in row 3
' row 1 is a header row and row 2 is empty
' in your case with 2 header rows you will not need this but I included this to
' illustrate how it works
torow = 3

For r = fromrow To lastrow
If Sheets("sheet1").Range("H" & r) = "Completed" Then
        Sheets("sheet2").Range("A" & torow & ":Z" & torow).Value = _
        Sheets("sheet1").Range("A" & r & ":Z" & r).Value
        torow = torow + 1
End If
Next r

End Sub
Sub copyvalues2()

Dim torow As Long
Dim r As Long

torow = 3   'value to row
r = 3       'value from row

Do
If Len(Sheets("sheet1").Range("A" & r)) = 0 Then Exit Sub
If Sheets("sheet1").Range("H" & r) = "Completed" Then
        Sheets("sheet2").Range("A" & torow & ":Z" & torow).Value = _
        Sheets("sheet1").Range("A" & r & ":Z" & r).Value
        torow = torow + 1
End If
r = r + 1
Loop

End Sub

 

[Brianwarnock]

BTW as all approaches are overwriting the previous data and there may be less you may want to automate the learing of that data first with someting like

Sheets("sheetname").Columns("A:Z").Clear

Brian

 

[lemo]

thanks Brian.

it's almost funny at this point.
i create a file with two sheets, sheet1 and sheet2, populate sheet1 with some data, try the first of your macros, and it works, smooth as a swiss watch..
i then copy your macro to my original file, change the names of the sheets, and - ERROR right after it copies the first instance of 'Completed'! i mean, literally, it pastes the row, then decides that it's done! (i checked using your suggested 'step through' method, thanks). same goes for the second macro.
i don't know what to think anymore.
l

 

[Brianwarnock]

Can you attach your workbook?

Ah! I'm not on 2007/10, but still might be worth attaching it if you can.

Brian

 

[lemo]

attaching.. i saved it as '97-03 file, so hopefully you should be able to use it..
thanks,
l

 

[Brianwarnock]

It fails because your order date Col U is null resulting in a negative date in col x , it does not like that!!

I changed the formula to

=IF(LEN(U4)=0,"",U4-WEEKDAY(U4,2)+1)

and all works well, but I must ask why are you keeping copies of the same data on two sheets , also each time you run the macro you will copy the values of all rows including those already copied?


Brian

 

[lemo]

aaah, i knew it was something trivial !!

thanks, Brian the savior!..

this is just the beginning, eventually the macro will copy data based on different criteria into different tabs and/or files, and erase them from the ALLORDERS tab..

l

 

[Brianwarnock]

Have you thought of using Move?

Whether you use move or a separate Delete, it is much easier to code the For ... next loop starting at the last row using the Step parameter as you then do not have to alter the lastrow and row number depending on whether a row has been deleted or not.

For r = lastrow to 4 Step -1
.
.code
.
Next r

Brian

 

[lemo]

thanks, will look into that..

actually, somewhat related, i still haven't decided how i'm going to handle the rows that were copied (or moved) already - do i just delete them, or clear content and then sort the remaining data, etc..
if i understand your suggestion correctly, it won't matter which direction the For...Next loop is in the latter case, but will matter in the former.. or will it? bit confusing, but i think i'll figure it out, eventually..

l