Defining a Range with Cells(r,c)

  • Thread starter Thread starter Mark Parent
  • Start date Start date
M

Mark Parent

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
 
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
 
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"??
 
Back
Top