Adding continuous Series Collections with loop

[mattreingold]

Hello everyone, I am having quite the issue today. I have a workbook in which the first sheet is a data table, the second is a chart, and the third is a template for which this code copies per sample to analyze. Everything is working quite nice (I know some of this code is literally atrocious).

Below is a section of my code. The chart begins blank, however formatted.

I want with each sample run (each run through the 'm' loop) to add a data series. What is interesting is that the legend updates with each run, populating the legend with a new line and new name, however, for some reason the previous data series x and y values get overwritten with the most recent loop values.

If interested, I attached the full workbook, however I tried to upload a few files to play around with (they are from a materials testing machine sourced .mtwData - my code opens it without displaying alerts, forcing it to open in the read only format, as it should) but the cite didnt allow me, so I uploaded a couple here:
http://www.mediafire.com/file/ldhaaq...5-58_1.mtwData.
http://www.mediafire.com/file/3jy4k4...0-01_1.mtwData

I also attached a picture to show how it is overwriting each data series.

I would very much appreciate some help with this, I feel as if I made a simple mistake, however I cannot find where....

Sub RunReport()


' Establish All Workbook and Worksheet Variables


Dim WBT As Workbook ' This Workbook
Dim WBD As Workbook ' Data Workbook
Dim WSD As Worksheet ' Data Sheet from data workbook
Dim WPN As Worksheet ' Report, in WBT
Dim NewSheet As Worksheet ' Sheet being created


Set WBT = Workbooks("RunCompressionTestingReport.xlsm")


Dim specimenName As String, specimenSheetNameMTW As String, specimenNameTesting() As String, specimenNameNoFileType() As String


Dim xlFileFirst As Variant, xlFileSecond As Variant
Dim fso As New FileSystemObject


Counter = 0
numberOfFiles = InputBox("Enter Number of Samples to Analyze (1-30)")


'Execute for every sample
For m = 1 To numberOfFiles


    If checkRow = 3 Then
        Set NewSheet = WBT.Sheets(WBT.Sheets.Count)
        NewSheet.Name = specimenName
    Else
        WBT.Sheets(Counter + 2).Copy WBT.Sheets(WBT.Sheets.Count)
        Set NewSheet = WBT.Sheets(WBT.Sheets.Count)
        NewSheet.Range("C36:D15000").ClearContents
        NewSheet.Name = specimenName
        FixPreviousName() = Split(WBT.Sheets(WBT.Sheets.Count - 1).Name, " (2)")
        FixedName = FixPreviousName(0)
        WBT.Sheets(WBT.Sheets.Count - 1).Name = FixedName
    End If


    '''''''''''''


    Set WBD = Workbooks(fileNameWBD)


    ' Establish and Copy Data Sheet from .mtwData Workbook to RunReport Workbook
    Set WSD = WBD.Worksheets(specimenSheetNameMTW)


    ' Sets Sheet 1 of Report Workbook to WPN variable
    Set WPN = WBT.Worksheets("Sheet1")
    
    ' Create arrays and find starting points for load/time etc.
    Dim loadArr(), areaArr(), stressArr(), strainArr(), lengthArr(), changeLengthArr(), percPosArr(), timeArr()
    Dim firstRow As Long, lastRow As Long, columnIdx As Long


    ' Establish begining and end of data columns in .mtwData workbook
    firstRow = 3 ' Row after header in .mtwData file
    lastRow = WSD.Cells(Rows.Count, "A").End(xlUp).Row
    


'''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''


    ' Format by closing Data workbooks, deleting temporary data sheets and setting focus to 'Report' tab
    Application.DisplayAlerts = False
    Workbooks(fileNameWBD).Close
    
    With Chart1
        .ChartType = xlXYScatterLinesNoMarkers
        .SeriesCollection.Add _
        Source:=Sheets(Counter + 3).Range("C36:C15000")
        .SeriesCollection(Counter + 1).XValues = _
        Sheets(Counter + 3).Range("D36:D15000")
        .SeriesCollection(Counter + 1).Name = specimenName
    End With
    
    ' Reactivate alerts and set workbook focus to the first sheet
    Application.DisplayAlerts = True
    Counter = Counter + 1
    
Next m


' Name Chart Sheet to 'Data Plots' And Activate Sheet 1
Chart1.Name = "Data Plots"
Worksheets("Sheet1").Activate


End Sub

NOTE: If you do try to run the code, a prompt should ask you how many specimens to run, enter 2 to see how it overwrites.



Second.jpgFirst.jpg

[mattreingold]

Also, the macro of interest is 'RunReport' not 'AddSample' if you do try to run it.

[mattreingold]

Issue diagnosed, needed to use arrays to hold the data ranges (somehow they were getting overwritten).

Code that worked for me:

Dim YValueArray(), XValueArray()
    YValueArray = WBT.Sheets(m + 2).Range("C36:C15000").Value
    XValueArray = WBT.Sheets(m + 2).Range("D36:D15000").Value
    With Chart1
        .ChartType = xlXYScatterSmoothNoMarkers
        .SeriesCollection.NewSeries
        .SeriesCollection(m).Name = specimenName
        .SeriesCollection(m).XValues = XValueArray
        .SeriesCollection(m).Values = YValueArray
    End With

Reference: https://stackoverflow.com/questions/...ies-to-a-chart (not my post)