Change Chart links to values using VBA?

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

WSF

Excel 97
I have a worksheet with a collection of productivity charts based on
data in other worksheets of the same workbook.

I need to copy / email the worksheet with the charts only to other staff
- all they need is the static charts.

So I patiently covert the links in the charts to values by selecting the
chart line, going to the formula bar, press F9 then Enter to do so.

Surely there is a better way?

Can I do this laborious job in VBA?

Any ideas gratefully appreciated.

WSF
 
Hi,

This routine will convert links to array values.

Sub ChartValues()
Dim intSeries As Integer

With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
.Values = .Values
.XValues = .XValues
.Name = .Name
End With
Next
End With
End Sub

Cheers
Andy
 
Thanks for that Andy.
It doesn't seem to want to work on some charts but.
My problem is that I have up to 6 small charts per worksheet, rather
than in a chart Sheet.

I do recall a post a long while back that offered code to go through any
number of charts on the active worksheet and change all the chart links
to array values.

WSF
 
Hi,

It is possible the routine will fail if the chart contains empty cells.

Here is an extension of the code to process all charts on a worksheet.
Note I have note tested the amendments.

Sub ChartValues()
Dim intSeries As Integer
Dim objTemp as chartobject

For Each objTemp in Activesheet.chartobjects
With objtemp.Chart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
.Values = .Values
.XValues = .XValues
.Name = .Name
End With
Next
End With
Next
End Sub

Cheers
Andy
 
Back
Top