This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the source data from charts and graphs and list this data separately. This is an espcially important feature if you only have a chart but not the source data or if the source data was damaged but you still need it separate from the chart. This macro will work for source data that has been linked to or has been lost or simply resides on an external worksheet or workbook.
IMPORTANT
Create a new sheet titled ChartData (this is where the chart data will be placed)
To run the macro simply select the chart from which you want to pull data, either an embedded chart or a chart on its own tab, and then run the macro and the source data will be placed in the "ChartData" worksheet.
Sub GetChartValues()
'This macro will retrieve the source data from a chart in excel
'This works for charts where the source data has been lost or
'damaged.
'Simply select the chart and run the macro - make sure to create a
'separate worksheet titled "ChartData" first though.
'
Dim NumberOfRows As Integer
Dim X As Object
Counter = 2
NumberOfRows = UBound(ActiveChart.SeriesCollection(1).Values)
Worksheets("ChartData").Cells(1, 1) = "X Values"
With Worksheets("ChartData")
.Range(.Cells(2, 1), _
.Cells(NumberOfRows + 1, 1)) = _
Application.Transpose(ActiveChart.SeriesCollection(1).XValues)
End With
For Each X In ActiveChart.SeriesCollection
Worksheets("ChartData").Cells(1, Counter) = X.Name
With Worksheets("ChartData")
.Range(.Cells(2, Counter), _
.Cells(NumberOfRows + 1, Counter)) = _
Application.Transpose(X.Values)
End With
Counter = Counter + 1
Next
End Sub