making a chart using data from lots of sheets

[JordanGoodchild]

i want to make a chart that uses data from several sheets. the data is in the same spot on every sheet i.e. range("E2").

is there a general way in VBA to do this. so far i have:

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox1.Value).Range("E2")
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox2.Value).Range("E2")
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox3.Value).Range("E2")
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox4.Value).Range("E2")
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox5.Value).Range("E2")
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox6.Value).Range("E2")
    ActiveChart.SetSourceData Source:=Sheets("" & Me.ComboBox7.Value).Range("E2")
    ActiveChart.SeriesCollection(1).Name = "Protiens"
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="" & Me.TextBox1.Value

Each combobox picks the sheets that are available.

 

[Jon Peltier]

You need to set up a consolidated table in one sheet and plot from this table, or you need to add each value as a one-point series.

This tells a little bit about the issue: Charting Data From Different Sheets.