If I know where the range should be, I'd use this:

Set rngRescodeLookup = workbooks("prep_labor_actuals.xls") _
.worksheets("sheet1").range("RES_CODE_LOOKUP")

I find that much easier than having to parse and build the string myself.

sugargenius wrote:

Thanks Dave for your suggestions. It turns I had made a sheet level
name, so I think it was using the range on the current sheet to lookup
in. I was thinking Sheet1!$A$1 was sheet level name.

I like your way of referring to the range so I can use constants for
macro book name and lookup range name. I was doing something like:
Set rngRescodeLookup = Range("prep_labor_actuals.xls!RES_CODE_LOOKUP")

thanks,
Woody

--

Dave Peterson