VBA Plot Chart in Excel Using Data from Another Workbook

Hi,

I have a code supplied by http://peltiertech.com/ and I have well adapted it to my needs. The new requirement now is I want to plot a chart using the data from another workbook, using this code. This code asks for a defined range, that however is dynamically referenced and plots the data in the same sheet it took data from in the same workbook. Now, how can I use data from another workbook to plot chart?

The working code is here:

Sub NormPlot()

    Dim rngDataSource As Range, mybook As Workbook, ws As Worksheet, s As Long
    Dim iDataRowsCt As Long, sFileName As Variant
    Dim iDataColsCt As Integer, LastCol As Integer, j As Long, y As Long
    Dim iSrsIx As Integer, LastRow As Long
    Dim chtChart As Chart
    Dim srsNew As Series
    Dim lLoopStep As Long
    Dim lOffset As Long
    
    Set mybook = ActiveWorkbook
    sFileName = mybook.Name
   s = mybook.Worksheets.Count
For y = 2 To s
mybook.Sheets(y).Activate

    LastCol = Cells(3, Columns.Count).End(xlToLeft).Column
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    If InStr(1, sFileName, "12h30", vbTextCompare) > 0 Then
        '---> filename contains "12h30""
        lLoopStep = 20
        lOffset = 15
    ElseIf InStr(1, sFileName, "19h", vbTextCompare) > 0 Then
        '---> filename contains "19h"
        lLoopStep = 20
        lOffset = 17
        
    Else
        '---> filename contains neither
        MsgBox "Error", vbOKCancel + vbCritical
        Exit Sub
    End If

    For j = 3 To LastCol Step lLoopStep
        Range(Cells(1, j), Cells(LastRow - 3, j + lOffset)).Select

    If Not TypeName(Selection) = "Range" Then
        '' Doesn't work if no range is selected
        MsgBox "Please select a data range and try again.", _
            vbExclamation, "No Range Selected"
    Else
        Set rngDataSource = Selection
        With rngDataSource
            iDataRowsCt = .Rows.Count
            iDataColsCt = .Columns.Count
    End With
        If iDataColsCt Mod 2 > 0 Then
            MsgBox "Select a range with an EVEN number of columns.", _
                vbExclamation, "Select Even Number of Columns"
            Exit Sub
        End If

        '' Create the chart
        Set chtChart = ActiveSheet.ChartObjects.Add( _
            Left:=ActiveSheet.Columns(ActiveWindow.ScrollColumn).Left + _
                ActiveWindow.Width / 4, _
            Width:=ActiveWindow.Width / 3, _
            Top:=ActiveSheet.Rows(ActiveWindow.ScrollRow).Top + _
                ActiveWindow.Height / 4, _
            Height:=ActiveWindow.Height / 2).Chart

    With chtChart
            .ChartType = xlXYScatterLines
             .Parent.Name = Cells(2, j - 2).Text
          'First add the data series here
            .HasTitle = True
            .ChartTitle.Text = ActiveSheet.Cells(2, j - 2).Text
            .PlotArea.Select
            Selection.Width = 400
            Selection.Top = 27.857
            Selection.Height = 241.253
            .Legend.Select
            Selection.Left = 343.054
            Selection.Top = 43.655
    With .ChartArea.Format.Line
           .Visible = msoCTrue
           .Style = msoLineSingle
           .Weight = 1
    End With
    With .PlotArea.Format.Line
        .Visible = msoCTrue
        .Style = msoLineSingle
        .Weight = 1
    End With
    With .Axes(xlValue)
    With .TickLabels: .Font.Size = 8: .Font.Bold = False: End With
                .HasTitle = True
                .AxisTitle.Characters.Caption = "Densité de probabilité"
                .AxisTitle.Characters.Font.Size = 10
                .HasMajorGridlines = False
    End With
                             
    With .Axes(xlCategory)
    With .TickLabels: .Font.Size = 8: .Font.Bold = False: End With
                .HasTitle = True
                .AxisTitle.Characters.Caption = "Ecart(MW)"
                .AxisTitle.Characters.Font.Size = 10
                .HasMajorGridlines = False
    End With

            '' Remove any series created with the chart
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop

            For iSrsIx = 1 To iDataColsCt - 1 Step 2
                '' Add each series
                Set srsNew = .SeriesCollection.NewSeries
    With srsNew
                    .Name = rngDataSource.Cells(1, iSrsIx)
                    .Values = rngDataSource.Cells(2, iSrsIx + 1) _
                        .Resize(iDataRowsCt - 1, 1)
                    .XValues = rngDataSource.Cells(2, iSrsIx) _
                        .Resize(iDataRowsCt - 1, 1)
                    .MarkerStyle = xlMarkerStyleNone
    End With
            Next
        End With
    End If
    Next j
    Next y
End Sub

I don't even have an idea how this mechanism would work. Please any help would advance me on this.

Or is it possible to copy the plotted chart to another workbook with reference? I have these charts already plotted elsewhere, I just want some of them in another workbook as per user needs. So, if it's possible to copy the chart along with 3 rows of data below it, my job will be done!

Thanks in advance.
Sanjeev