Hi JH

Use the range name in the code.
See Debra's site for a example
http://www.contextures.com/xlNames01.html#Dynamic


--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Ron,
I moved the code into a larger spreadsheet I have on this line
Set rng =
ws1.Range("=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$ A:$A),COUNTA(Sheet1!$1:$1))")
I get an error message: runtime error '9" subscript out of range.
If you'll notice I used the dynamic code for figuring out the range.
Any thoughts?
Thanks again.
JH