Syntax Help: Range variables (plucking data and pasting to 2nd sheet)

[Finny]

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

 

[Finny]

Anyone know?

 

[Finny]

I can't see what's wrong with it.

Anyone know?

 

[Finny]

The error is
Run-time 1004
Application-defined or object-defined error

 

[Dave Peterson]

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 here:
'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]

Thanks Dave
I understand that error now.
Qualifying the cells, got it.
That first copy was left in by mistake from when I was testing.

I don't quite understand what you mean in terms of pasting.
Are you saying that my paste statements will paste the range referenced
in that same line to whatever cell is active? and ignore the clipboard?

To be clear I'm trying to copy range in sheet 1 to sheet 2 (src to dst)
....
I tried pastespecial instead of paste and it worked fine. wierd.

 

[Dave Peterson]

I'm saying that range(cells(1,1)) won't refer to A1 unless the value in
cells(1,1) is the string "A1". This syntax is the same as
range(cells(1,1).value).

Either use just Range("a1") or cells(1,1)--and you want the cells() version.

And I did miss the .paste vs .pastespecial values stuff. Sorry.