Create graph with data from 100 sheets - macro and loop?

Trincazz

New Member
Joined
Mar 5, 2019
Messages
2
I have a data set with individual measurements of over 100 pieces - each in a single sheet.
I need to create line chart with all data plotted in a single graph.
The name of each sheet is in cell J1
Y-Axis range is J2:J256
X-Axis range is C2:C256
How can I create a marco which automatically plots all data in one graph? Any ideas?

I was thinking of recording macro by plotting graph from 1 of the sheet and then create a loop with recorded macro which creates plots on the same graph with all the multiple sheet data? Can anyone help :)
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,010
Hello

Code:
Sub Trcz()
Dim ch As Shape, ws As Worksheet, sn%
Set ch = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines)
ch.Chart.HasTitle = 0
For Each ws In ActiveWorkbook.Worksheets
    ch.Chart.SeriesCollection.NewSeries
    sn = ch.Chart.FullSeriesCollection.Count
    ch.Chart.FullSeriesCollection(sn).Name = ws.Name
    ch.Chart.FullSeriesCollection(sn).XValues = "=" & ws.Name & "!$c$2:$c$10"
    ch.Chart.FullSeriesCollection(sn).Values = "=" & ws.Name & "!$j$2:$j$10"
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,978
Messages
5,545,312
Members
410,676
Latest member
M0J0jojo
Top