Excel Range Variables

[Guest]

My problem is trying to use a range variable with the Cells object. See the
code sample code below. I am trying to copy a group of cells from a sheet
that is not active. it works if I reference one cell but not more than one.
I can't seem to get the syntax right. hel would be appreciated.

Sub UseARangeVariable()

Dim MyRange As Range

Sheets("Rota").Select
Range("A10").Select
Set MyRange = ActiveCell.CurrentRegion

Sheets("Sheet1").Select 'Change Sheets

MyRange.Cells(1, 1).Copy 'Refer to first sheet i.e. Rota
Range("a1").Select
ActiveSheet.Paste 'But Paste to sheet1...this works

MyRange.Range(Cells(1, 2), Cells(1, 7)).Copy 'Try to do same with cell range
Range("a2").Select 'but causes 1004 error
ActiveSheet.Paste
End Sub

 

[Tom Ogilvy]

MyRange.Range(MyRange.Cells(1, 2), MyRange.Cells(1, 7))

unqualified Cells refers to the active sheet.

 

[Alan Beban]

Range(MyRange(1,2),MyRange(1,7)).Copy

Alan Beban

 

[Guest]

Many Thanks Alan Beban...exactly what I needed

 

[Alan Beban]

Many Thanks Alan Beban...exactly what I needed

You're welcome. The general lesson is that once the range has been
assigned to an object variable (i.e., in this case MyArray), the Cells
Method is superfluous; the object variable carries its own qualification
with it.

Alan Beban

 

[Tom Ogilvy]

the Cells Method is superfluous


with noted exceptions.

Set MyRange = Range("A1:A10").Columns
? Range(MyRange(1,2),MyRange(1,7)).address '<== Raises an error
? MyRange.Range(MyRange.Cells(1, 2), MyRange.Cells(1, 7)).Address
$B$1:$G$1

 

[Alan Beban]

with noted exceptions.

Set MyRange = Range("A1:A10").Columns
? Range(MyRange(1,2),MyRange(1,7)).address '<== Raises an error
? MyRange.Range(MyRange.Cells(1, 2), MyRange.Cells(1, 7)).Address
$B$1:$G$1

Yes indeed! I should have said "once a range that is a collection of
cells is assigned to an object variable . . . ."

Thanks for pointing it out,
Alan Beban