Excel VBA Pasted Chart is not the same as source chart

Question

When copying a chart generated by my macro and pasting as a picture (to avoid chart rendering while scrolling as a lot of data points are involved), the pasted picture of the chart does not display the same data.

I am a novice excel vba user, so I'm probably doing something incorrect here...

In just excel, I have tried right-clicking my chart and pasting as a picture and receive the same issue. I think there might be a limitation with the amount of data I'm working with (~11k data points), but since I'm not receiving an error I'm not sure.

I have tried different methods of copying the chart in excel vba (chartarea.copy, chart.copypicture) with no success.

Here is part of the code of interest....

'This is a small snippet of a much larger range of code, certain ranges/variables are defined earlier


Set localDate = Sheets(1).Range("A2:A" & lastRow)
Set plasmaNaVisRange = plasmaNaRange.SpecialCells(xlCellTypeVisible)
Set plasmaNaChart = Sheets(4).Shapes.AddChart.Chart

'Clears automatic charting done on source sheet
plasmaNaChart.ChartArea.ClearContents

With plasmaNaChart
  .ChartType = xlXYScatter
  .SetSourceData Source:=Range(localDate, plasmaNaVisRange),     PlotBy:=xlColumns
  .SetElement (msoElementChartTitleAboveChart)
  .ChartTitle.Text = "Plasma"
  .Parent.Height = 276
  .Parent.Width = 466
  .Axes(xlCategory).TickLabels.Orientation = 45
End With

'Forces correct assignment of axes
With plasmaNaChart.SeriesCollection(1)
   .XValues = localDate
   .Values = plasmaNaVisRange
   .Name = "Na"
End With

'Everything appears correct up to this point and chart displays as     corrected

plasmaNaChart.ChartArea.Copy

Sheets(4).Range("B36").Select
Sheets(4).Pictures.Paste
plasmaNaChart.Parent.Delete

On the original chart I see all my data points with correct axes (y-axis ranges around 0-160, x-axis lists the dates correctly). On the pasted chart I have no Y-values and confirmed as much when I pasted it keeping source and found nothing in the y-series. Also my x-axis is completely messed up and has dates ranging from 1/0/1900 to 11/21/2036

Didn't work unfortunately, still get the same results with no Y-values and a very wompy x-axis with non-sensical dates — Cappucino_Jack
If you pause the code before the Copy, does the chart look OK? — Tim Williams
Yessir, its only when I copy/paste does it have a problem. Every single paste option makes the chart set Y-series = {0} , regardless with keeping source formatting, as picture, and every other paste special option. — Cappucino_Jack
Sorry no more suggestions - I've done this multiple times and not had this problem. — Tim Williams

Cappucino_Jack Cappucino_Jack · Accepted Answer · 2019-05-14T23:35:27

Solved! I managed to circumvent this issue by trying a slightly different approach to my problem. Here was the root cause...

The number of data points wasn't the issue that I had initially suspected, instead it was that the range of data I was trying to plot was too complex. With over 10k data points broken up by an autofilter, the data series was a huge mess of references to the worksheet and individual ranges created by the PlasmaNaVisRange = PlasmaNaRange.SpecialCells(xlCellTypeVisible) .

Instead, I stuck with the basic range (PlasmaNaRange) which was the filtered column I was trying to extract and graph. Using the chart.PlotVisibleOnly = true method, I was able to first chart the basic range, then plot only visible data points, saving my chart from having an overly complex data series. The revised code looks like like this...

Set localDate = Sheets(1).Range("A2:A" & lastRow)
Set plasmaNaChart = Sheets(4).Shapes.AddChart.Chart

'Clears automatic charting done on source sheet
plasmaNaChart.ChartArea.ClearContents

With plasmaNaChart
   .ChartType = xlXYScatter
   .SetSourceData Source:=Range(localDate, plasmaNaRange), PlotBy:=xlColumns
   .SetElement (msoElementChartTitleAboveChart)
   .ChartTitle.Text = "Plasma"
   .Parent.Height = 276
   .Parent.Width = 466
   .PlotVisibleOnly = True         'Saves me from creating a filtered variable with a complex data series
   .Axes(xlCategory).TickLabels.Orientation = 45
End With

'Forces correct assignment of axes
With plasmaNaChart.SeriesCollection(1)
   .XValues = localDate
   .Values = plasmaNaRange
   .Name = "Na"
End With

Excel VBA Pasted Chart is not the same as source chart

1 Answers