Reset Chart Range to Original Data via VBA without losing formatting

[NatetheGreat]

Hello,

Sheets("Charts").Select
   Range("A20").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range("Stats[RP]")
    'Insert 2nd Chart below 1st Chart, as a line chart with RP as first series
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    ActiveChart.Parent.Name = "Pos vs Cum B/A"
    ActiveChart.ChartTitle.Text = "Pos vs Cum B/A"
    'Name the chart, title the chart and position title header
    With ActiveChart.Parent
    .Top = Sheets("Charts").Cells(25, 1).Top
.Left = Sheets("Charts").Cells(1, 1).Left
    .Height = Sheets("Charts").Range("A15:A42").Height
    .Width = Sheets("Charts").Range("A42:q42").Width
    'Size chart appropriately
    
    ActiveChart.SeriesCollection.Add _
        Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum A]]")
'Add Cum A (Will Go in default as a line series (series2) )
ActiveChart.SeriesCollection.Add _
        Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum B]]")
'Add Cum Bid (Will Go in default as a line series as well (series3) )
    ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
    ActiveChart.Legend.Select
    Selection.delete
'Get Rid of Unnecessary legend from 2nd Chart
    
    
    
    End With
    
  
   
   ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
   ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
    ActiveChart.SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 255)
    'changes Cum B (series3) to column Chart and colour to blue
     

   ActiveChart.SeriesCollection(2).Select
   ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
      ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
   ActiveChart.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
   'Change Cum A (series2) to column Chart and color to red
    
    
    ActiveChart.SeriesCollection(1).Format.Line.Weight = 1.5
    ActiveChart.SeriesCollection(1).Border.Color = RGB(255, 255, 0)
    ActiveChart.PlotArea.Format.Fill.Transparency = 0.8
    
    ' Changes line thickness and transparency and color
  
    ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
    ActiveChart.SeriesCollection(1).Name = "=""RP"""
    ActiveChart.SeriesCollection(2).Name = "=""Cum A"""
    ActiveChart.SeriesCollection(3).Name = "=""Cum B"""
    'Change labels of Series on RollPos CO/Chart

All that this code does, is build my multi-series chart and format it.

I then run the following code which will zoom to a specific part of the chart as specified by certain user-defined values in the cells C57 & C58

 Sub SecndChartResize()
Sheets("charts").Activate
 With ActiveSheet.ChartObjects("Position vs Cumiliative Bid/Offer").chart
        .SetSourceData Source:=Sheets("Raw Data").Range("w" & Sheets("Charts").Range("c57").Value + 15 & _
            ":w" & Sheets("charts").Range("c58").Value + 15 & _
            ",x" & Sheets("charts").Range("c57").Value + 15 & _
            ":x" & Sheets("charts").Range("c58").Value + 15 & _
            ",y" & Sheets("charts").Range("c57").Value + 15 & _
            ":y" & Sheets("charts").Range("c58").Value + 15)
    End With
End Sub

I then have a button, which brings the chart back to its normal state i.e. all the raw data range it was set to upon creation ( Stats[#all][[RP], Stats[#all][[Cum B], Stats[#all][[Cum A])

Macro assigned to Button

 Sub testsecondresizeredo()
Sheets("charts").Activate
ActiveSheet.ChartObjects("Pos vs Cum B/A").Activate
 ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(1).delete
    ActiveChart.SeriesCollection(1).delete
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).delete
ActiveSheet.ChartObjects("Position vs Cumiliative Bid/Offer").Activate
 ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range("Stats[RollPos]")
 ActiveChart.SeriesCollection.Add _
        Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum A]]")
'Add Cumiliative Ask (Will Go in default as a line series (series2) )
ActiveChart.SeriesCollection.Add _
        Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum B]]")
    

End Sub

The problem is that simply deleting each of the series (And I have no idea why the macro recorder has put the series deletion all against collection(1) even though they are 3 separate ones...but that's for another day) , and adding them back in again as full series, resets all the formatting that was applied to the chart upon creation. Is there anyway to tell excel via vba to retain the original formatting? Or because that formatting was series specific, upon deletion it is lost forever and does not apply to the new series that replace them?


Alternatively if anyone sees any way to significantly enhance this process, in addition to answering my post question.... then that will be welcomed with open arms.


Forgive me for the long post and ugly code.... I am very new to VBA and am trying to stitch bits of recorded macros, and google searches together to build my worksheet.


Many Thanks All,

Nate

 

[Andrew Poulsom]

I don't know why you need to delete the Series, but if you delete SeriesCollection(1) the second series becomes SeriesCollection(1), and so on. Why can't you use a variation of your code that resizes the Series to plot all the data?

 

[NatetheGreat]

I don't know why you need to delete the Series, but if you delete SeriesCollection(1) the second series becomes SeriesCollection(1), and so on. Why can't you use a variation of your code that resizes the Series to plot all the data?

Hi Andrew,

I swear that one of these days I will look back on my old posts (hopefully much wiser and with more exposure), and laugh at the fact that I Replaced all the above with

ActiveSheet.ChartObjects("Pos vs CumB/A").Activate
   
    ActiveChart.SetSourceData Source:=Sheets("Raw Data").Range( _
        "Stats[[RP]:[CB]]")

Oh well.... live and learn