Yep, the named ranges were created on another sheet to facilitate the
conditional formatting workaround where the CF can't reference data on other
sheets. It looks like the HTML XL-Word-XL trick will work, so at least I've
got a workaround.

Thank you for the earlier suggestion- I'll use that in the future if I run
into a similar situation without non-local named ranges.

Thanks,
Keith


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
The named ranges must be in another sheet or are global names otherwise
they
would copy over with the sheet.

Is that the case?


Gord

On Mon, 5 Mar 2007 13:57:34 -0500, "Keith R"
wrote:

Gord-

Thank you for your response; the problem is that the created copy still
references the named ranges, which do not exist in the new workbook. As
soon
as the old workbook closes (taking with it the named ranges) the new sheet
loses the coloration based on the conditional format (which relies on the
named ranges).

Thanks,
Keith

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Keith

Don't copy/paste.

Right-click on the sheet tab and "move or copy"

Checkmark "create a copy" To BookNew Workbook.


Gord Dibben MS Excel MVP

On Mon, 5 Mar 2007 12:33:23 -0500, "Keith R"
wrote:

I have a workbook that uses conditional formatting (cell color) based on
values in named ranges. Now I need to copy one of the sheets out of the
workbook for a user. I can't provide the whole workbook (even with that
data
hidden) because data in other parts of the workbook are confidential.

I tried copy/paste that sheet to a new workbook (thinking I could just
not
update the links) but the colors in te cells of the destination workbook
only maintain their coloration while the old workbook is open (and
therefore
the named ranges are available for evaluation). Once the other workbook
closes, the conditional formatting can no longer access those named
ranges
and they lose their color.

Is there a way to copy/paste a worksheet while retaining the coloration,
similar to [paste special/values] for cell contents? Ultimately I need
something like [paste special/current color] which doesn't exist, but
hopefully that helps explain what I'm looking for.

Any ideas?

Thanks,
Keith