Thanks David,
I changed the code to:

Sub movedata()
Dim S2Row As Long
Dim Copies As Integer
Dim Paul As Integer
Dim RowToCopy As Integer

RowToCopy = 1
For Paul = 1 To RowToCopy
S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row

Worksheets("sheet1").Range("A7:I7").Select
Selection.Copy
Worksheets("sheet2").Activate
Worksheets("sheet2").Range("A" & S2Row + (Paul -
1)).PasteSpecial(xlPasteValues)

Next Paul
End Sub
The code does everything I want except to paste the range onto the
next available row on worksheet2. It's either all the way up with xlUp or on
row 65536 with xlDown.
Can you help one more time?

"David Heaton" wrote:

Paul,

The reason on A7 is being copied across is because thats all you are asking
it to do.

Worksheets("sheet2").Range("A" & S2Row + Paul).Value

selects only the cell A & (S2Row+Paul). Which in your case is A7.

You need to amend this to be a range identical to the range you are copying
from , i.e Worksheets("sheet2").Range("A7:I7").Value would work fine


you have a loop running from 1 to RowToCopy, but you've declared RowToCopy
to be 1, so essentially your loop is 'For Paul =1 to 1. You may want the
option to copy multiple rows later which is why this is here. If not, try
this for copying ranges.

S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row
Worksheets("sheet1").Range("A7:I7").Select
Selection.Copy
Worksheets("sheet2").Activate
Worksheets("sheet2").Range("a" & S2Row).Activate
ActiveSheet.Paste

hth

David


"Paul3rd" wrote in message
...
Thanks for your reply Don,
I'm having a bad day though;
where in the code would I insert row+1?

"Don Guillett" wrote:

row+1

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Paul3rd" wrote in message
...
Hello,
I'm trying to automate a worksheet. I have a command button that calls
a
module,
(Module2), the code selects a range of cells (Range("A7:I7") and copies
that
range
to the next empty row of worksheet2 in the workbook.
I can only get cell A7 to copy correctly, none of the remaining
Range(B7:I7), copy over.
The code is as follows:
Option Explicit

Sub movedata()
Dim S2Row As Long
Dim Paul As Integer
Dim RowToCopy As Integer

RowToCopy = 1
S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row

For Paul = 1 To RowToCopy
Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _
Worksheets("sheet1").Range("A7:I7" & Paul).Value

Next Paul

End Sub

I then tried:
Option Explicit

Sub movedata()
Dim S2Row As Long
Dim Paul As Integer
Dim RowToCopy As Integer
Dim MyRange As Range

Set MyRange = ActiveSheet.Range("A7:I7")
RowToCopy = 1
S2Row = Worksheets("sheet2").Range("A1").End(xlDown).Row

For Paul = 1 To RowToCopy
Worksheets("sheet2").Range("A" & S2Row + Paul).Value = _
Worksheets("sheet1").Range(MyRange).Value

Next Paul

End Sub
But that gives me a RunTime Error #1004
"Application-Defined" or "Object-Defined" error.

Thanks in advance for any help,