What am I doing wrong?
 

Hi All,
I am using Excel 2000 with Windows XP. I have the following code to capture
the actual range of cells

Range("A5",Range("G5").End(xlDown)).Select

This works well. Also the following works well.

Set sourceRange=mybook.worksheets(5).Range("A5").Curre ntRegion

In both the above instances the workbook in aopen and active.

Applying the same logic as follows does not work for me but gives me an
"Application or Object defined error". mybook is a closed Workbook.

Set sourceRange=mybook.Worksheets(5).Range("A5",Range( "G5").End(xlDown))

Any ideas what am I doing wrong.

Thanks in advance/sgl

What am I doing wrong?
 

Hi SGL,

Try changing:

Set sourceRange=mybook.Worksheets(5).Range("A5",Range( "G5").End(xlDown))

to:

With mybook.Sheets(5)
Set SourceRange = .Range("A5", .Range("G5").End(xlDown))
End With

Your problem is one of qualification: in your original expression:

Range("G5")

is not qualified (with its parent sheet) and, therefore, this is interpreted
as relating to cell G5 on the active sheet. If the active sheet is not
sheet(5), then you are asking VBA to construct a range which spans 2 sheets.
Since this is not possible, you get the error message.


---
Regards,
Norman



"sgl" wrote in message
...
Hi All,
I am using Excel 2000 with Windows XP. I have the following code to
capture
the actual range of cells

Range("A5",Range("G5").End(xlDown)).Select

This works well. Also the following works well.

Set sourceRange=mybook.worksheets(5).Range("A5").Curre ntRegion

In both the above instances the workbook in aopen and active.

Applying the same logic as follows does not work for me but gives me an
"Application or Object defined error". mybook is a closed Workbook.

Set sourceRange=mybook.Worksheets(5).Range("A5",Range( "G5").End(xlDown))

Any ideas what am I doing wrong.

Thanks in advance/sgl

What am I doing wrong?
 

Nrman,
You saved my day !. Thanks a lot for the quick response. Worked a treat
Regards/sgl

"Norman Jones" wrote:

Hi SGL,

Try changing:

Set sourceRange=mybook.Worksheets(5).Range("A5",Range( "G5").End(xlDown))

to:

With mybook.Sheets(5)
Set SourceRange = .Range("A5", .Range("G5").End(xlDown))
End With

Your problem is one of qualification: in your original expression:

Range("G5")

is not qualified (with its parent sheet) and, therefore, this is interpreted
as relating to cell G5 on the active sheet. If the active sheet is not
sheet(5), then you are asking VBA to construct a range which spans 2 sheets.
Since this is not possible, you get the error message.


---
Regards,
Norman



"sgl" wrote in message
...
Hi All,
I am using Excel 2000 with Windows XP. I have the following code to
capture
the actual range of cells

Range("A5",Range("G5").End(xlDown)).Select

This works well. Also the following works well.

Set sourceRange=mybook.worksheets(5).Range("A5").Curre ntRegion

In both the above instances the workbook in aopen and active.

Applying the same logic as follows does not work for me but gives me an
"Application or Object defined error". mybook is a closed Workbook.

Set sourceRange=mybook.Worksheets(5).Range("A5",Range( "G5").End(xlDown))

Any ideas what am I doing wrong.

Thanks in advance/sgl