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

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

unqualified Cells refers to the active sheet.

--
Regards,
Tom Ogilvy

"Alan" wrote in message
...
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
--
Alan

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

Alan Beban

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

Many Thanks Alan Beban...exactly what I needed

"Alan" wrote:

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

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

"Alan" wrote:


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

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


--
Regards,
Tom Ogilvy


"Alan Beban" wrote in message
...
Alan wrote:
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

"Alan" wrote:


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

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


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