Thread
:
RowToCopy problem
View Single Post
#
6
Posted to microsoft.public.excel.newusers
Don Guillett
external usenet poster
Posts: 10,124
RowToCopy problem
Send me a workbook along with clear instructions of what you want with
before and after examples
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Paul3rd" wrote in message
...
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,
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett