Paste Special Chart? 3

[TomYC]

I have a chart on one worksheet, whose data comes from a different worksheet. Before sending the chart out, I'd like to delete the worksheet and "freeze" the chart. Is this possible? Right now I am hiding and protecting the data worksheet, but as I have many of these, and each must be done individually, I'd rather find a way to "flatten" my chart so that it remains as (as a picture?) even when the data disappears...

 

[combo]

Open new excel instance. Paste chart as (extended) metafile (please note that it is not possible in the same application instance, so another one needed). BTW, you can paste as metafile to other applications, as powerpoint or word.

combo

 

[Loomah]

another option, using vba in this case, is to save the chart as a picture and paste that into your sheet

Sub marine()
Application.EnableEvents = False
With Worksheets("Sheet1").ChartObjects(1)
    .Chart.Export Filename:="G:\Documents\Tests\Tests_for_TT\MyChart.gif", FilterName:="GIF"
    .Delete
End With
With Range("A1")
    .Select
    .Parent.Pictures.Insert ( _
        "G:\Documents\Tests\Tests_for_TT\MyChart.gif")
End With
'remove the saved chart
Kill "G:\Documents\Tests\Tests_for_TT\MyChart.gif"
Application.EnableEvents = True
End Sub

this would need some extra testing to see if there's a chart there to start with etc etc.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlhelp]

Yes, you can divorce the chart from the the data. Requires a tiny amount of work.

You need to select a data series,
[tab]1. Click anywhere in the formula bar,
[tab]2. Hold the control key and press the "equal sign"
[tab]3. Hit Enter

Repeat with each data series.

A man has only two choices: He can be right or he can be happy.

 

[TomYC]

Thanks to you both--
Neither of these options is optimal, of course, but I will make a try with both of them. I was hoping for a 'edit select all--paste special values only' variant. But I'll keep at it!
Regards==T

 

[combo]

Another solution based on xlhelp's idea:
1. make the chart a separate sheet (right-click it and set new sheet option,
2. copy sheet to new workbook (right-click sheet's tab),
3. break links (edit>links) for copied chartsheet.

combo

 

[xlhelp]

break links (edit>links) for copied chartsheet

Now why didn't I think of that?

Learn something new every day.

=====> ["Color Purple"] * [/"Color Purple"]

A man has only two choices: He can be right or he can be happy.

 

[Lilliabeth]

In Excel 2007, you can copy then Paste as Picture

--Lilliabeth

 

[Lilliabeth]

In Excel 2003, if you just copy and then 'Paste Picture' (hold down shift and click Edit to see this choice), the "picture" is still linked. You have to 'Copy Picture' (again, shift+Edit), then just Paste.


In 2007, you can Copy then use the Paste button to choose 'As Picture'

--Lilliabeth

 

[TomYC]

Thanks for the suggestion there!
I'm in 2003, and I haven't seen any combination of keys that copies the chart as a picture, much less permits pasting it as one. Still trying--

 

[Lilliabeth]

Hold down the Shift key and click the Edit menu.

--Lilliabeth

 

[TomYC]

Now that was a learning experience! Thanks, lilliabeth--now I can't wait to find out if I can automate/macro-ize this technique...