Hi Helpers,
I create a chart that use StatterSmoothNoMarkers type. The xvalue is fixed @ location A2: A100.
But the Series value is depended on number of files opened. If they open 3 files, i have 3*3=9 columns data and so on. I got the code from online and modified it for my case. I dont know how to put all collected data together to make them become data source. Could you please help me out.
Thanks. Here is the line with the error:
Set rngChtData = ("A2:A100") , ("2"): intCol5.Select ------->error right here

Sub addchart()
Dim myChtObj As ChartObject 
Dim rngChtData As range 
Dim rngChtXVal As range
Dim iColumn As Long
Dim intCol5 As Integer
n = 3   'a number of files opened
intCol5 = intCol5 + (n * 3 + 2)  'number of columns is count

If TypeName(Selection) <> "Range" Then Exit Sub
Set rngChtData = ("A2:A100") , ("2"): intCol5.Select

Set rngChtData = Selection
    With rngChtData
Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
    End With
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
    With myChtObj.Chart
.ChartType = xlXYScatterSmoothNoMarker

Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
For iColumn = 2 To rngChtData.Columns(n).Count
    With .SeriesCollection.NewSeries
.Values = rngChtXVal.Offset(, iColumn - 1)
.XValues = rngChtXVal

.Name = rngChtData(1, iColumn)
    End With
    intCol5 = intCol5 + 1
Next
myChtObj.Activate
End With

End Sub