This post presents a VBA procedure that extracts all data from a chart, and places it into a new worksheet. Why would you want to extract a chart’s data? Sometimes a chart gets its data from diverse sources, and you’d like to have the data in one place. You may receive a workbook that contains a chart, but the chart’s data is in a workbook which you don’t have access to. For whatever reason, this procedure comes in handy.
Sub ExtractChartData() Dim iSrs As Long Dim cht As Chart Dim srs As Series Dim ws As Worksheet If ActiveChart Is Nothing Then Exit Sub Set cht = ActiveChart Set ws = Worksheets.Add For iSrs = 1 To cht.SeriesCollection.Count Set srs = cht.SeriesCollection(iSrs) On Error Resume Next ws.Cells(1, 2 * iSrs).Value = srs.Name ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.XValues) ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _ WorksheetFunction.Transpose(srs.Values) Next End Sub
The procedure merely extracts the data, it does not change the data source of the chart to the ranges in the new worksheet. If desired, you could embellish this macro, so it restores information such as chart type, series formatting (marker or fill style and colors, line or border styles and colors), axis types and scales, axis and chart titles, data labels, and more.
If you are not sure how to use this procedure, read How To Use Someone Else’s Macro.