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

Question

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.

TrowaD · Accepted Answer

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

Don't Know · Answer

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.

TrowaD · Answer

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

TrowaD · Answer

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.

Auto. copy a row of data onto another sheet using conditions

4 responses

Subscribe To Our Newsletter!