On Apr 3, 10:29*pm, Sarah (OGI)
wrote:
I've got a workbook with multiple worksheets. *For every one worksheet with a
pivot table, there is a corresponding worksheet containing a chart relating
to that data source.

Via a macro, I'd like to be able to select all sheets containing '(Chart)'
in the worksheet name and copy them out into another workbook. *Would I do
this by selecting each sheet and doing a 'move/copy' to a new workbook? *
Once the sheets are exported, is there an easy way to remove all links to the
source data?

The idea is to use the initial workbook to update the source data and charts
on a monthly basis, then distribute the charts as a separate document. *The
recipients of such information (both internal and external contacts) should
not be able to access the original source data.

Many thanks in advance - my vb skills/knowledge is limited.

Cheers

Hi Sarah,

I'm sure I'll soon be corrected, but to my understanding you can't
have a chart without the underlying data being stored in a sheet
somewhere (pivot table / range etc). The only thing I can think of if
you absolutely must separate it from the data would be to copy the
chart object as a picture object and paste it into another sheet. I
don't think you could pase to a 'chart' sheet though, it would have to
just be a normal worksheet that you are pasting the object onto.

The code would go something like this for the actual copy process:

Sub CopyChart()
ActiveChart.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
Sheets("Sheet2").Select
ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", _
Link:=False, DisplayAsIcon:=False
End Sub

Cheers,
Ivan.