Fix for colors changing on copy/paste?

[Daryl K]

I have a standard bar chart in Excel 2013 where one element of the series is intentionally set a different color (the series are the months of the current year, while the "stand apart" one is an average of the previous year - for comparison).

Any time I copy it, the pasted version has the differently-colored series element in a different location. it's the weirdest bug I have ever seen. Print Preview looks like it should (colors in the right place).

I should also point out that this sheet is normally protected, and I have a button which activates a macro to copy to the clipboard, using this code:

[CODE]Sub CopyMonitorChart()
    Worksheets("Charts").ChartObjects("Chart 1").CopyPicture    
End Sub

[/CODE]

The actual data behind the chart is huge, so the copy-as-picture is intentional, since they never want to embed the workbook in PowerPoint. I have also tried the CopyPicture instruction using the xlPrinter and xlScreen modifiers, but again, it looks wrong.

As an experiment, I also tried manually copying and pasting, using Keep Source Formatting and Use Destination Theme paste methods, and those actually mess up the formatting in a different way: all series elements are uniform in color, meaning the one different one is lost. Only paste As Picture keeps the differently-colored element...but in the wrong location (same as the code above).

All of this tells me there is some fundamental bug in the way the clipboard copies chart formatting. I've been Googling this for hours looking for a workaround, and since I've come up blank I expect no answer to this post either, but figure it can never hurt to ask.

Has anyone figured a way around this??

 

[blanchdm]

That is beyond bizarre.

You could always try a screenshot or a snipping tool (no good for the macro, obviously, but it would do the trick manually). I've never heard of that kind of problem before, though. Post an answer if you find one - now I'm curious!

 

[Daryl K]

That is beyond bizarre.

You could always try a screenshot or a snipping tool (no good for the macro, obviously, but it would do the trick manually). I've never heard of that kind of problem before, though. Post an answer if you find one - now I'm curious!

Thanks for the reply.

Yeah, a screenshot tool does indeed "solve" the immediate issue, but it sort of defeats my goal of making it a simple click-and-paste operation. This is part of a tool I created that many manufacturing site leads use to put together monthly summary PowerPoint slides, so it has to be quick and easy.

Until I figure out a solution (if ever) I've dropped the differently-colored bar for the previous FY baseline and just made them all the same color. Strange this has not come to Microsoft's attention yet...

 

[mole999]

is it a standard colour. or one that has been tweaked, probably something to do with stock themes

 

[Daryl K]

All are standard colors. Nothing tweaked or adjusted, as the standard color picker has plenty of choices (IMHO).

 

[blanchdm]

I was poking around a little bit more on my own with some complex graphs I work with - what version of Excel are you using? I'm on Excel 2010 and I see more accurate formatting when I do this:

Copy as Picture -> Appearance: as shown on screen -> Format: bitmap

If I choose Format: picture, things I've done with line weights get undone, but the bitmap works well for me. Have you tried playing around with those options?

 

[Daryl K]

I was poking around a little bit more on my own with some complex graphs I work with - what version of Excel are you using? I'm on Excel 2010 and I see more accurate formatting when I do this:

Copy as Picture -> Appearance: as shown on screen -> Format: bitmap

If I choose Format: picture, things I've done with line weights get undone, but the bitmap works well for me. Have you tried playing around with those options?

I am using 2013.

Actually I had tried it. In the case of my macro it's the modifier "xlBitmap" immediately after the "CopyPicture" command (i.e. Worksheets("Charts").ChartObjects("Chart 1").CopyPicture xlBitmap).

Alas, this works the same as the first paste-as-picture option: it keeps the one differently-colored bar, but moves it to an entirely different element in the chart!