3 Attachment(s)
Adding continuous Series Collections with loop
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....
Code:
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.
Attachment 22454Attachment 22453
