Auto. copy a row of data onto another sheet using conditions [Solved/Closed]

DON'T KNOW - Apr 20, 2015 at 05:48 PM - Latest reply:  Don't Know
- Apr 28, 2015 at 04:33 PM
Hello,
I am new to VBA and I have two spreadsheets both in the same workbook. One sheet is full off information the other is a printable form that needs only the information that meets certain Criteria, such as if on "sheet 1", the options amount is more than "0" then the adjoining row is copied onto "sheet 2".
I know this is a simple Macros. Any help would be appreciated.
THANKS.
See more 

8 replies

Best answer
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Apr 21, 2015 at 10:51 AM
2
Thank you
Hi DON'T KNOW,

In the code below the options amount is presumed to be in column A.
When the value in column A is bigger then 0 then the entire row is copied to the first available row on sheet2.

Here is the code:
Sub RunMe()
Dim lRow As Integer

Sheets("Sheet1").Select
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
    If cell.Value > 0 Then
        cell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next cell

End Sub


Best regards,
Trowa

Thank you, TrowaD 2

Something to say? Add comment

CCM has helped 1705 users this month

Hello TrowaD
I put in the code that you gave and I think you have got me on the right track, but I think that I am doing something wrong.
I am trying to copy rows from a sheet titled "Order Form" to a sheet titled "Printable Form".
The Options are in the "Order Form" between rows 8-64. The amount is in column "A". I need it to be moved (Only if the amount is >0) to the "Printable Form" between rows 15-43.
I appreciate any help that you give.
Thanks.
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Apr 23, 2015 at 10:58 AM
1
Thank you
Hi Don't Know,

Ok, I amended the code according to your requirements.

The code will loop through column A rows 8-64 of sheet Order Form.
Whenever the value is bigger then 0 the entire row is being copied.
The code will then look for a free row on sheet Printable From.
The free row will be based on the value in column A; If the cell is empty and between 15-43, then the copied row will be pasted as values.
If there is no free row then a message box will appear, letting you know that the code will be aborted.

Here is the code:
Sub RunMe()
Dim x As Integer

Sheets("Order Form").Select

For Each cell In Range("A8:A64")
    If cell.Value > 0 Then
        cell.EntireRow.Copy
        For x = 15 To 44 Step 1
            If Sheets("Printable Form").Range("A" & x) = vbNullString Then
                Sheets("Printable Form").Range("A" & x).PasteSpecial (xlValues)
                GoTo NextCell
            End If
            
            If x = 44 Then
                MsgBox "Rows 15-43 are already filled with data. Macro will abort."
                Exit Sub
            End If
        Next x
    End If
NextCell:
Next cell

Application.CutCopyMode = False
End Sub

Give it a try and see if we solved your query.

Best regards,
Trowa
Wow thanks,
This one is a lot closer. I put the code in and it said, "to do this all the merged cells have to be the same size." I don't have any merged cells.
So I then tried to experiment with the code you gave. Here is what I did.
I worked mainly on this part of it.

If Sheets("Printable Form Process").Range("A" & x) = vbNullString Then
Sheets("Printable Form Process").Range("A" & x).PasteSpecial (xlValues)
GoTo NextCell

I changed the range in both From ("A"&x) to ("A1"&x), when I did this it copied only the last qualifying row, and it pasted it on row 1415.
So then I changed them again to ("A5"& x), It copied all that I wanted, but it again pasted it in row that were not on the sheet like 114-120. it got everything else correct.
I am new to this so I may be missing something any help is appreciated. Thanks.

Don't Know
TrowaD 2391 Posts Sunday September 12, 2010Registration dateModeratorStatus July 12, 2018 Last seen - Apr 28, 2015 at 11:18 AM
1
Thank you
Hi Don't Know,

Good to see you worked things out!

To copy certain columns instead of the entire row, we will look
at code line 8:
cell.EntireRow.Copy
Change this into:
Range(Cells(cell.Row, "A"), Cells(cell.Row, "G"))

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Thanks it worked like a charm
0
Thank you
Hello TrowaD
I put in the code that you gave and I think you have got me on the right track, but I think that I am doing something wrong.
I am trying to copy rows from a sheet titled "Order Form" to a sheet titled "Printable Form".
The Options are in the "Order Form" between rows 8-64. The amount is in column "A". I need it to be moved (Only if the amount is >0) to the "Printable Form" between rows 15-43.
I appreciate any help that you give.
I know I am asking a lot, I only want what is in the cell and not the function copied.

Thanks.
Hello TrowaD
It was a mistake that I made when I put in your code I fixed my mistake and it did exactly what it was supposed to do. I do have one more question. How do I Copy/Paste it with a limit to how many columns it. I only need to copy from columns "A" to "G" because I have functions in columns "I" to "J".
You have been a "God Sent" in this already, thanks.