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,