Scatter of data series from different sheets

[Khaledalaydi]

Hello guys,

I have an excel of many sheets with the same structure.
I am creating a sheet with graph to plot the same xy of different sheets as a series in the same graph.

so the graph will have all the series of all the sheets. I have found something on the internet but it is drawing a graph on everysheet.

Sub graph()

'start with the first sheet
Application.ScreenUpdating =FalseForEach sh In Worksheets
sh.Activate


'find the collength for the series entryDim collength AsInteger
collength = Cells(Rows.Count,"A").End(xlUp).Row
'clean up the data'Inserting a Column at Column C
Range("C1").EntireColumn.Insert
[C1].Value ="time"

'eqn for first row in column
[C2].Value ="=if(B2>0,24*(B2-$B$2))"'autofill rest of columns and format data to general
Range("C2:C"& collength).FillDown
Columns(3).NumberFormat ="General"

'create the chart as an object in the worksheetDim myChtObj As ChartObjectSet myChtObj = ActiveSheet.ChartObjects.Add _(Left:=100, Width:=375, Top:=75, Height:=225)
myChtObj.Chart.SetSourceData Source:=Sheets(ActiveSheet.Name).Range("C2:Q"& collength)
myChtObj.Chart.ChartType = xlXYScatterLines


With myChtObj.Chart

' make an XY chart.ChartType = xlXYScatterLines

' remove extra seriesDoUntil.SeriesCollection.Count =0.SeriesCollection(1).DeleteLoopWith.SeriesCollection.NewSeries.Name = ActiveSheet.Range("Q1").Values = ActiveSheet.Range("Q2:Q"& collength).XValues = ActiveSheet.Range("C2:C"& collength)EndWithEndWith


'go to the next sheetNext sh
Application.ScreenUpdating =True

End Sub

 

[vletm]

Khaledalaydi
Can You do Your needs manually?
Have You tried to record Macro from Your needs?
You could notice 'which' part of code should 'modify'.
... and without even a sample file this would be a challenge, how to test?

 

[p45cal]

To get you started, in the attached, see button on Sheet1 and data on the 3 other sheets.
Click button to run this macro:

Sub blah()
Set myChart = Sheets("Sheet1").Shapes.AddChart2(240, xlXYScatterLines)
For Each sht In Sheets(Array("Sheet2", "Sheet3", "Sheet4"))
  With myChart.Chart.SeriesCollection.NewSeries
    .XValues = "='" & sht.Name & "'!$C$5:$C$12"
    .Values = "='" & sht.Name & "'!$D$5:$D$12"
  End With
Next sht
End Sub