VBA for Adding/deleting data series collections on one graph

[Chris Macro]

I have the below macro that takes data and plots it on a graph. I am trying to make the macro plot multiple data series (multiple lines) at one time on one graph with the variability of increasing or decreasing the amount of series each time it is run (the amount of series is determined by the user). I am stuck at trying to figure out how to go about adding a new series at the end of my looping variable "Z" and how to delete the previous amounts of series before entering into the "Z" loop (leaving just one data series associated with the graph). If I am approaching this completely wrong I am open to changing my methodologies.


Code: (start where the comment states "Update Graph")

<font face=Calibri><SPAN style="color:#00007F">For</SPAN> Z = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(SelectionArray)<br>    <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets(SelectedStmt)<br>        <SPAN style="color:#007F00">'Determine Last Column</SPAN><br>            LastColumn = .Cells(2, .Columns.Count).End(xlToLeft).Column<br>        <br>        <SPAN style="color:#007F00">'Find Row number of variable "Account"</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Statement = "Income Stmt (YTD)" <SPAN style="color:#00007F">Or</SPAN> Statement = "Income Stmt (QtoQ)" <SPAN style="color:#00007F">Or</SPAN> Statement = "Income Stmt (MtoM)" <SPAN style="color:#00007F">Then</SPAN><br>                GraphRow = WorksheetFunction.Match(SelectionArray(Z), .Range("B:B"), 0)<br>                <SPAN style="color:#007F00">'GraphTitle = .Cells(GraphRow, "B")</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                GraphRow = WorksheetFunction.Match(SelectionArray(Z), .Range("B:B"), 0)<br>                <SPAN style="color:#007F00">'GraphTitle = .Cells(GraphRow, "B")</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#007F00">'Determine Column Locations for selected "Year" range</SPAN><br>            x = 1<br>            <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> LastColumn<br>                <SPAN style="color:#00007F">If</SPAN> GoByMonth = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> Year(.Cells(1, y)) <= EndYear And Year(.Cells(1, y)) >= StartYear <SPAN style="color:#00007F">Then</SPAN><br>                        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> X_Axis_Array(1 <SPAN style="color:#00007F">To</SPAN> x)<br>                        X_Axis_Array(x) = y<br>                        x = x + 1<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                    <br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Else</SPAN><br>                    <SPAN style="color:#00007F">If</SPAN> .Cells(1, y) <= EndMonth And Year(.Cells(1, y)) >= StartYear <SPAN style="color:#00007F">Then</SPAN><br>                        <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> X_Axis_Array(1 <SPAN style="color:#00007F">To</SPAN> x)<br>                        X_Axis_Array(x) = y<br>                        x = x + 1<br>                    <SPAN style="color:#00007F">Else</SPAN><br>                    <br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Next</SPAN> y<br>    <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#007F00">'Update Graph</SPAN><br><br>    <SPAN style="color:#007F00">'Clear Previous Data</SPAN><br>        ActiveWorkbook.Sheets("The Trender").Rows("33:40").ClearContents<br>        <br>    <SPAN style="color:#007F00">'Pull Selected Ranges</SPAN><br>        X_Axis_ArrayEnd = <SPAN style="color:#00007F">UBound</SPAN>(X_Axis_Array)<br>    <br>        <SPAN style="color:#00007F">For</SPAN> y = 1 <SPAN style="color:#00007F">To</SPAN> X_Axis_ArrayEnd<br>            <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets("The Trender")<br>                <SPAN style="color:#007F00">'Month Title</SPAN><br>                .Cells(33 + m, y) = Sheets(SelectedStmt).Cells(2, X_Axis_Array)<br>                <br>                <SPAN style="color:#007F00">'Amount Data Point</SPAN><br>                    .Cells(34 + m, y) = Sheets(SelectedStmt).Cells(GraphRow, X_Axis_Array) * SignChange<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> y<br>    <br>    <SPAN style="color:#007F00">'Insert Ranges into Graph</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets("The Trender")<br>            <SPAN style="color:#00007F">Set</SPAN> ChartData = .Range(.Cells(34 + m, 1), .Cells(34 + m, X_Axis_ArrayEnd))<br>            <SPAN style="color:#00007F">Set</SPAN> TimeStamp = .Range(.Cells(33 + m, 1), .Cells(33 + m, X_Axis_ArrayEnd))<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>        <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Sheets("The Trender").ChartObjects("Chart 1")<br>            .Activate<br>            ActiveChart.SeriesCollection(Z).Values = ChartData<br>            ActiveChart.SeriesCollection(Z).XValues = TimeStamp<br>            ActiveChart.SeriesCollection(Z).Name = GraphTitle<br>            <SPAN style="color:#007F00">'ActiveChart.ChartTitle.Text = GraphTitle</SPAN><br>        End <SPAN style="color:#00007F">With</SPAN><br>m = m + 2<br><SPAN style="color:#007F00">'add new series collection</SPAN><br><SPAN style="color:#00007F">Next</SPAN> Z</FONT>

 

[Chris Macro]

I was able to figure this out. Sorry if you had already started working on this.