Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditional formatting source data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditional formatting source data
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditionalformatting source data
Maybe...
If you can select the range and copy into MSWord, you'll see the CF coloring kept. Then you can copy from MSWord and paste into your new workbook. 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditional formatting source data
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditional formatting source data
Dave-
thank you- this works (although the range is so large it is unreadable in word, but it does copy over). I'm in Excel 2003 now, hopefully 2007 allows us to specify an HTML-only copy/paste directly within Excel without going to Word first. Maybe 2003 allows that too, but I'm not smart enough to figure out how ;-) Thanks, Keith "Dave Peterson" wrote in message ... Maybe... If you can select the range and copy into MSWord, you'll see the CF coloring kept. Then you can copy from MSWord and paste into your new workbook. 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditional formatting source data
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy/paste want to retain cell coloration but lose the conditional formatting source data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting -- copy paste special won't change referenc | Excel Discussion (Misc queries) | |||
How do I copy & paste formulas without them linking to the source | Excel Discussion (Misc queries) | |||
Copy paste Conditional Formatting | Excel Discussion (Misc queries) | |||
Copy Paste Conditional Formatting | Excel Discussion (Misc queries) | |||
How do I copy and paste Excel charts and retain formatting? Mine . | Charts and Charting in Excel |