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

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 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

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 wrote:
> 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 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

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.



Finny wrote:
>
> 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 wrote:
> > 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 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

--

Dave Peterson