Hi all,

I have the following code

Dim MyBook as Workbook
Dim SrcRange as Range
Dim ShtName as Variant
Dim N as Interger

.... Code ...

set
SrcRange=Mybook.Workshhets(ShtName(N)).Range(Range ("A2"),Range("A2").SpecialCells(xlLastCell))

.... morwe code

This gives me an Application or Object Defined Error. If I use the following
it works well but I do not get the specific range I need.

set SrcRange=Mybook.Workshhets(ShtName(N)).UsedRange

Can someone pleasse help with this as it is driving me barmy

Thanx in Advance/sgl

SrcRange=Mybook.Workshhets(ShtName(N)).Range(Range ("A2"),Range("A2").SpecialCells(xlLastCell))
Hi,
have you tried to make this ?

SrcRange =
MyBook.WorkSheets(ShtName(N)).Range("A2).Specialce lls(xlastcell)

Hope this will help you

Sorry this doesn't work It only selects the last cell in the range whereas I
need to select the whole range "Block"

"VoTiger" wrote:


SrcRange=Mybook.Workshhets(ShtName(N)).Range(Range ("A2"),Range("A2").SpecialCells(xlLastCell))
Hi,
have you tried to make this ?

SrcRange =
MyBook.WorkSheets(ShtName(N)).Range("A2).Specialce lls(xlastcell)

Hope this will help you

Set SrcRange = Mybook.Worksheets(ShtName(N)).Range(Range("A2"),
Range("A2").SpecialCells(xlLastCell))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"sgl" wrote in message
...
Hi all,

I have the following code

Dim MyBook as Workbook
Dim SrcRange as Range
Dim ShtName as Variant
Dim N as Interger

... Code ...

set

SrcRange=Mybook.Workshhets(ShtName(N)).Range(Range ("A2"),Range("A2").Special
Cells(xlLastCell))

... morwe code

This gives me an Application or Object Defined Error. If I use the
following
it works well but I do not get the specific range I need.

set SrcRange=Mybook.Workshhets(ShtName(N)).UsedRange

Can someone pleasse help with this as it is driving me barmy

Thanx in Advance/sgl

The range("a2") references are unqualified. So they belong to the
activesheet--which may not be mybook.worksheets(shtname(n)) (watch your spelling
of worksheets, too!)

I'd use:

with mybook.worksheets(shtname(n))
Set SrcRange = .Range(.Range("a2"), _
.Range("a2").SpecialCells(xlCellTypeLastCell))
end with

or

with mybook.worksheets(shtname(n))
Set SrcRange = .Range("A2", .cells.SpecialCells(xlCellTypeLastCell))
end with

sgl wrote:

Hi all,

I have the following code

Dim MyBook as Workbook
Dim SrcRange as Range
Dim ShtName as Variant
Dim N as Interger

... Code ...

set
SrcRange=Mybook.Workshhets(ShtName(N)).Range(Range ("A2"),Range("A2").SpecialCells(xlLastCell))

... morwe code

This gives me an Application or Object Defined Error. If I use the following
it works well but I do not get the specific range I need.

set SrcRange=Mybook.Workshhets(ShtName(N)).UsedRange

Can someone pleasse help with this as it is driving me barmy

Thanx in Advance/sgl

--

Dave Peterson

Thank you all for your assistance. I realised my mistake after posting. I
have used a slightly different coding which seems to work

with mybook.worksheets(shtname(n))
Set SrcRange = .Range("a2", .Range("a2").SpecialCells(xlLastCell))
end with

thanx again/sgl

"Dave Peterson" wrote:

The range("a2") references are unqualified. So they belong to the
activesheet--which may not be mybook.worksheets(shtname(n)) (watch your spelling
of worksheets, too!)

I'd use:

with mybook.worksheets(shtname(n))
Set SrcRange = .Range(.Range("a2"), _
.Range("a2").SpecialCells(xlCellTypeLastCell))
end with

or

with mybook.worksheets(shtname(n))
Set SrcRange = .Range("A2", .cells.SpecialCells(xlCellTypeLastCell))
end with

sgl wrote:

Hi all,

I have the following code

Dim MyBook as Workbook
Dim SrcRange as Range
Dim ShtName as Variant
Dim N as Interger

... Code ...

set
SrcRange=Mybook.Workshhets(ShtName(N)).Range(Range ("A2"),Range("A2").SpecialCells(xlLastCell))

... morwe code

This gives me an Application or Object Defined Error. If I use the following
it works well but I do not get the specific range I need.

set SrcRange=Mybook.Workshhets(ShtName(N)).UsedRange

Can someone pleasse help with this as it is driving me barmy

Thanx in Advance/sgl

--

Dave Peterson