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,