Syntax Help: Range variables (plucking data and pasting to 2ndsheet)
You have unqualified ranges in your code.
worksheets(1).range(cells(....
the .range portion is qualified with worksheets(1). But Cells() isn't qualified
by anything--and if this code is in a general module, that unqualified range
will refer to the activesheet.
So at leaste one of these will blow up:
worksheets(1).range(cells(...
or worksheets(2).range(cells(...
Since they both can't be active.
In your case, it looks like you don't need .range() in all your statements.
Dim srcRowNum As Integer
Dim dstRowNum As Integer
srcRowNum = 19
dstRowNum = 2
Do Until rownum 600
'ITEM CODE
'you have two .copy statements. The first isn't used!
'Cells(srcRowNum + 1, 1).Copy
Worksheets(1).Cells(srcRowNum + 1, 1).Copy
Worksheets(2).Cells(dstRowNum, 1).Paste
'but you'll want to qualify the range he
'WEEKLY
with worksheets(1)
.range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy
end with
Worksheets(2).Cells(dstRowNum, 2).Paste
srcRowNum = srcRowNum + 6
'and again here
'MONTHLY
with Worksheets(1)
.Range(.Cells(srcRowNum, 4), .Cells(srcRowNum, 16)).Copy
end with
Worksheets(2).Cells(dstRowNum, 17).Paste
srcRowNum = srcRowNum + 8
dstRowNum = dstRowNum + 1
Loop
Note the dots in front of the .cells() portion. That means that that range
(.cells(srcRownum,4) belongs to the object in the previous With statement--in
this case worksheets(1).
I did change the logic of your code, though.
This line:
Worksheets(2).Range(Cells(dstRowNum, 17)).Paste
Says to pick up the value from cells(dstRowNum,17) from the activesheet. It
would have to look like an address (A17, X99) and then you'd be left with
worksheets(2).range("x99").paste
But I didn't think that's what you really wanted to do.
Finny wrote:
I think the code is straightforward in its intent.
I just want to pluck a cell value from sheet 1 to sheet 2
Then a range to sheet 2
then another range to sheet 2
How can this be done?
Thanks in advance.
Dim srcRowNum As Integer
Dim dstRowNum As Integer
srcRowNum = 19
dstRowNum = 2
Do Until rownum 600
'ITEM CODE
Range(Cells(srcRowNum + 1, 1)).Copy
Worksheets(1).Range(Cells(srcRowNum + 1, 1)).Copy
Worksheets(2).Range(Cells(dstRowNum, 1)).Paste
'WEEKLY
Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum,
16)).Copy
Worksheets(2).Range(Cells(dstRowNum, 2)).Paste
srcRowNum = srcRowNum + 6
'MONTHLY
Worksheets(1).Range(Cells(srcRowNum, 4), Cells(srcRowNum,
16)).Copy
Worksheets(2).Range(Cells(dstRowNum, 17)).Paste
srcRowNum = srcRowNum + 8
dstRowNum = dstRowNum + 1
Loop
--
Dave Peterson
|