# copying cells from one worksheet to the next

#### bossierben

##### New Member
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

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Code:
``````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``````

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

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

Code:
``````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``````

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.

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

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

Replies
3
Views
147
Replies
8
Views
126
Replies
3
Views
207
Replies
18
Views
323
Replies
11
Views
635

1,203,383
Messages
6,055,107
Members
444,763
Latest member
Jaapaap

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back