Thread
:
RowToCopy problem
View Single Post
#
3
Posted to microsoft.public.excel.newusers
Paul3rd
external usenet poster
Posts: 28
RowToCopy problem
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,
Reply With Quote
Paul3rd
View Public Profile
Find all posts by Paul3rd