Converting Graph data links to values using code

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Excel 97
I create charts using a workbook with templates and change the reports using
dynamic data ranges. One worksheet can hold several graphs. I then copy the
charts to another workbook for distribution. I extinguish the data series
links by manually converting them to values (select graph line, F2, F9,
Enter) etc., graph by graph. A painstaking job at times.

Can this be automated with code?

TIA WSF
 
One way to convert all embedded charts on a sheet:

Sub Macro2()
Dim aSeries As Series, aChartObj As ChartObject
For Each aChartObj In ActiveSheet.ChartObjects
For Each aSeries In aChartObj.Chart.SeriesCollection
With aSeries
.Values = .Values
.XValues = .XValues
End With
Next aSeries
Next aChartOba
End Sub

Do note that the code above converts only the series. If there are
other objects (datalabels, titles, textboxes, etc.) with links to
cells, they are not converted.

An alternative you might want to consider is to copy an image of the
chart with SHIFT + Edit | Copy Picture...

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top