I have a spreadsheet with columns that extend beyond Z. I need to
programmatically select a range, and I opted not to use
COLN = "A"
sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z.

I'm using
sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most
places.

In this particular case, I need to select and copy / pastespecial cells from
another sheet, and I keep getting error 1004 "Method range". My code is
Set R=Sheets("other").Range(cells(row,coln),cells(row,coln+3)).Copy

Is the issue the use of Cells() within the Range, or the way I'm using the
other sheet?

You don't use "Set R = ..." and "....Copy" in the same command.

Just describe the range and end with copy...

Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Mark Parent" wrote:

> I have a spreadsheet with columns that extend beyond Z. I need to
> programmatically select a range, and I opted not to use
> COLN = "A"
> sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z.
>
> I'm using
> sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most
> places.
>
> In this particular case, I need to select and copy / pastespecial cells from
> another sheet, and I keep getting error 1004 "Method range". My code is
> Set R=Sheets("other").Range(cells(row,coln),cells(row,coln+3)).Copy
>
> Is the issue the use of Cells() within the Range, or the way I'm using the
> other sheet?
>

Watch out for those unqualified ranges:

Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy

The cells() will refer to the active sheet (if the code is in a general
module)--and Other may not be that active sheet.

I'd qualify those ranges this way:

with Sheets("other")
.Range(.cells(row, coln), .cells(row, coln+3)).Copy
end with
(the dots mean that those objects belong to the object in the previous With
statement. In this case, Sheets("other").)

Or

Sheets("other").cells(row, coln).resize(1, 4).Copy



JBeaucaire wrote:
>
> You don't use "Set R = ..." and "....Copy" in the same command.
>
> Just describe the range and end with copy...
>
> Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy
>
> --
> "Actually, I *am* a rocket scientist." -- JB
> (www.MadRocketScientist.com)
>
> Your feedback is appreciated, click YES if this post helped you.
>
> "Mark Parent" wrote:
>
> > I have a spreadsheet with columns that extend beyond Z. I need to
> > programmatically select a range, and I opted not to use
> > COLN = "A"
> > sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z.
> >
> > I'm using
> > sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most
> > places.
> >
> > In this particular case, I need to select and copy / pastespecial cells from
> > another sheet, and I keep getting error 1004 "Method range". My code is
> > Set R=Sheets("other").Range(cells(row,coln),cells(row,coln+3)).Copy
> >
> > Is the issue the use of Cells() within the Range, or the way I'm using the
> > other sheet?
> >

--

Dave Peterson

That's helping... but I'm still stymied...

This statement works, and copies a single cell:
Sheets("other").Cells(7, 4).Copy

But this statement to select a range instead of a single cell fails:


Sheets("other").Range(Cells(8, 4), Cells(40, 5)).Copy

I get a Method 'Range' of object 'Worksheet' failed.
If I can't set a range this way instead of Range("D8:E40"), how can I define
a range beyond column "Z"??


"JBeaucaire" wrote:

> You don't use "Set R = ..." and "....Copy" in the same command.
>
> Just describe the range and end with copy...
>
> Sheets("other").Range(cells(row, coln), cells(row, coln+3)).Copy
>
> --
> "Actually, I *am* a rocket scientist." -- JB
> (www.MadRocketScientist.com)
>
> Your feedback is appreciated, click YES if this post helped you.
>
>
> "Mark Parent" wrote:
>
> > I have a spreadsheet with columns that extend beyond Z. I need to
> > programmatically select a range, and I opted not to use
> > COLN = "A"
> > sheet.range( COLN & "5:" & COLN+3 & "5" ) to avoid issues beyond Z.
> >
> > I'm using
> > sheet.range(cells(row,coln),cells(row,coln+3)) which seems to work in most
> > places.
> >
> > In this particular case, I need to select and copy / pastespecial cells from
> > another sheet, and I keep getting error 1004 "Method range". My code is
> > Set R=Sheets("other").Range(cells(row,coln),cells(row,coln+3)).Copy
> >
> > Is the issue the use of Cells() within the Range, or the way I'm using the
> > other sheet?
> >