Chart Data

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.