RowToCopy problem

[Paul3rd]

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,

 

[Don Guillett]

row+1

 

[Paul3rd]

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

 

[David Heaton]

Paul,

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

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]

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?

 

[Don Guillett]

Send me a workbook along with clear instructions of what you want with
before and after examples

 

[David Heaton]

Ok,

to find the next available row in Sheet2 replace

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

with

Sheet2.Range("a1").Select
Selection.End(xlDown).Select
S2Row = Selection.Row + 1

This assumes you have no occasional empty rows (if you do let me know and
i'll give you the work around for that)

Also I still feel the For...Next loop is redundant . Can you let me know
why you need it.

Regards

David