VBA Excel 2010 - Paste graph into excel while keeping source formatting

Question

Im trying to paste my graph into the same excel spreadsheet but have it so it keeps the source formatting.

I tried recording a macro but that doesnt display any code when I paste, after googling I found that you can paste it into PowerPoint (Found here) the ExecuteMso can be explained in the link to MSDN.

The reason I need to paste the charts back into excel while keeping the souce formatting is I will need to make multiple charts but then get information from them, such as legend etc etc

Edit:

Code I have tried so far and comes up with one error or another

Sheets("Sheet1").ChartObjects(1).CopyPicture
Sheets("Sheet1").Paste Destination:=Worksheets("Sheet1").Range("I18")

ActiveSheet.ChartObjects(1).Activate
ActiveChart.ChartArea.Copy
'Application.CommandBars.ExecuteMso "PasteExcelChartSourceFormatting" 
'ActiveSheet.PasteSpecial Format:="Keep Chart Source Formatting" Link:=False _
DisplayAsIcon:=False
'ActiveSheet.PasteSpecial Format:="PasteExcelChartSourceFormatting" Link:=False _
DisplayAsIcon:=False

Answer 1

Keeping Source formatting will still keep the link to the cell contents enabled as charts can not hold values, only grab. That being, the only way to keep thew chart for reference is to either create a new chart from a new set of data, this may seem like a waste if you want different data sets with the same custom formatting, which you can speed up by creating a template from the original chart and then create off of that template for each new set of data, which you can automate as well after the data is set like this:

Sub Macro1()
'
' Macro1 Macro
'

'

ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.ApplyChartTemplate ( _
        "C:\Users\Username\AppData\Roaming\Microsoft\Templates\Charts\WhatYouNamedTheTemplate.crtx")Sheet1!$A$1:$C$4
ActiveChart.SetSourceData Source:=Range("SheetWhereDataIs!RangeYouWantChartToBeCreatedFrom EX:Sheet1!$A$1:$C$4")
End Sub

Pasting as an image may be your easiest way to present your charts though every time the data is set. If this is a constant task on that sheet might just want to create a button next to it linked to the macro.

Or alternatively if you would like to we could create a macro that clones the entire sheet and then copies the chart on there back to the original sheet while hiding the clone so that all the data is stored elsewhere and still have the data to tie itself to.

Sorry about all the workarounds for the task. Let me know if you think of any other ways you would like to approach this.

Answer 2

This might be a good place to start for you:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Copy
Range("Cell you want to paste to").Select
ActiveSheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
    DisplayAsIcon:=False

Let me know if this isn't what you are looking for.

As long as the link=False, it will not be directly linked to the content of origin, even if displayed on the sheet. If you do prefer for a new sheet for each chart then you can use this:

Sheets.Add After:=ActiveSheet
ActiveSheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
    DisplayAsIcon:=False

Or just give it a custom range on any sheet you deem fit:

Sheets("Sheet you want to paste to").Select
Range("Cell you want to paste to").Select
ActiveSheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
    DisplayAsIcon:=False

All in all the macro should look something relatively small and in the simplest form looks like this:

Sub YourMacro()
'
' PasteImage Macro
'

'
Sheets("Sheet you want to copy from").ChartObjects("Name of Chart").CopyPicture
Sheets("Sheet you want to paste to").Paste Destination:=Worksheets("Sheet you want to paste to").Range("Cell you want it to appear in")
End Sub

Let me know if this doesn't work for you.