Thank you,joel.its great,worked perfectly.

joel wrote:
Use Address with external = true. this gives the workbook as well so I added
code to remove the workbook.

LastRow = Range("H3").End(xlDown).Row
MyList = Range(Cells(3, 8), Cells(LastRow, 14)).Address(external:=True)
'remove workbook
MyList = Mid(MyList, InStr(MyList, "]") + 1)

this code is giving active sheet list.
LastRow = Range("H3").End(xlDown).Row
myList = Range(Cells(3, 8), Cells(LastRow, 14)).Address
what change is to be done to the above code if I want "sheet2 " list with
same parameters of above code.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200905/1