copying cells from one worksheet to the next

[bossierben]

Using Excel 2007, I am a slow learner Novice, so be patient. LOL

I have a worksheet called "Andersen" if there is a value of >0 (that is zero) in a13, I need it to copy the following cells in this order a13,z13,aa13,d13,to the worksheet called "Orders" and paste starting in a13 on Orders. Because there is heading information on Orders worksheet.

I need this to do this for every Row on the first worksheet Andersen. So if A14 >0, copy a14,z14,aa14,d14,to the worksheet called "Orders"

and so on through a3757

 

[patel45]

Sub a()
With Sheets("Andersen")
  For r = 13 To 3757
    If .Range("A" & r) > 0 Then
      .Range("A" & r & ", Z" & r & ", AA" & r & ", D" & r).Copy Sheets("Orders").Range("A" & r)
    End If
  Next
End With
End Sub

 

[bossierben]

I made a new button and pasted it in there and I get an error, compile error expected end sub, what did I do wrong

Private Sub CommandButton5_Click()
Sub a()
With Sheets("Andersen")
For r = 13 To 3757
If .Range("A" & r) > 0 Then
.Range("A" & r & ", Z" & r & ", AA" & r & ", D" & r).Copy Sheets("Orders").Range("A" & r)
End If
Next
End With
End Sub
End Sub

 

[Firefly2012]

You need to remove the Sub a() and End Sub:

Private Sub CommandButton5_Click()
With Sheets("Andersen")
For r = 13 To 3757
If .Range("A" & r) > 0 Then
.Range("A" & r & ", Z" & r & ", AA" & r & ", D" & r).Copy Sheets("Orders").Range("A" & r)
End If
Next
End With
End Sub

 

[bossierben]

yes I figured that out, sorry. , but the button works, however it is leaving spaces on the worksheet Orders. Example, if Andersen cell a13 is > 1 and say a200 >1, it does move both, but it leaves all the lines a14 through a199 on orders page blank.

 

[bossierben]

also how do I reset the form. So I can use it again for next customer.

 

[bossierben]

ok, it works, but I need it to not leave empty rows on the orders worksheet.