Auto. copy a row of data onto another sheet using conditions
Solved/Closed
Related:
- Auto. copy a row of data onto another sheet using conditions
- Grand theft auto 1 download - Download - Action and adventure
- How to transfer data from one sheet to another in excel using formula - Guide
- Little alchemy cheat sheet - Guide
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Facebook auto refresh - Guide
4 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 21, 2015 at 10:51 AM
Apr 21, 2015 at 10:51 AM
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:
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 23, 2015 at 10:58 AM
Apr 23, 2015 at 10:58 AM
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:
Give it a try and see if we solved your query.
Best regards,
Trowa
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
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Apr 28, 2015 at 11:18 AM
Apr 28, 2015 at 11:18 AM
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.
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.
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.
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.
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.
Apr 22, 2015 at 09:52 AM
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.