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