You need to use SET

Set Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")

Cheers
Andy

Craig wrote:
Thanks to all who replied, JNW, I wrote your piece of code into my workbook
but it gace an error called Object Variable or With block variable not set.
When I clicked debug it highlighted this line

Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")

Any ideas why it would do this?

Cheers


Craig

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info