The macro listed below assumes you have the following sheets in your workbook:
- Charts - to get the data extracted from the data sheets and produce
a chart. It can be (should be) blank.
- First - a blank sheet before the data sheets
- one or more data sheets
- Last - a blank sheet after the data sheets
The steps necessary to add the macro to your work sheet is as follows:
Tools -> Macro -> Visual Basic Editor
At this point, you should get some additional windows and the visual
basic editor will be running. If you don't have any existing macros,
Insert -> Module
The title of the window should be something like
Workbook1 - Module1 (Code)
(replace Workbook1 with the name of your workbook)
Copy / paste the macro below into this window. If you want a more
descriptive name for the macro, change the first line which now reads
to something more descriptive like
Excel -> Close and return to Microsoft Excel
This should bring up the spreadsheet again. You should only have to do
the steps up to this point once. The macro will be saved with the
workbook (and the data in the workbook).
The first time, or whenever you add one or more data sheets, run the macro using
Tools -> Macro -> Macros
which brings up a dialog box. The macro below is named Headsetsdotcom
[unless you renamed it], select it and click the Run button. The macro
will automatically switch to the Charts worksheet, fill in the top
section with the extracted data from the data sheets and add a line
plot of the data.
If you have problems up to this point - don't hesitate to ask for
clarification on the steps, describing what steps worked and what did
Now - the chart I produced might not be quite what you are looking
for. In the comments below (lines starting with '), I have included a
number of notes [numbers in square brackets] to describe how to
customize the macro. If you use
Tools -> Macro -> Macros
then select the Headsetsdotcom macro and click on Edit, it should put
you back into the Visual Basic editor. Scroll down to the notes and
make changes as appropriate.
 Column headings currently are E4 through E8; I expect you want
some other names here.
 If you add another Cell to plot (e.g., E9); add a line similar to
the other ones.
 Modify the chart type. You can use
View -> Object Browser
and enter xlcharttype as the search string (field at the top). The
lower right scrolling window should show the choices of chart types.
The names are pretty self explanatory but if you cannot find the one
you want - describe the type you want in a clarification request.
You might want to swap the rows / columns as well, use PlotBy := xlColumns instead.
 through  Update the chart, X-axis, and Y-axis titles as needed.
That should cover the customizations that I could think of. If this
does not quite meet your needs - please clarify them so I can update
the macro to better satisfy them.
' Headsetsdotcom Macro
' Macro recorded 3/29/2004 by Maniac
' Compute how many sheets we need to process
' While we are at it, copy the sheet names to the first column
FirstSheet = 0
NumberSheets = 0
For I = 1 To Sheets.Count
If Sheets(I).Name = "First" Then
FirstSheet = I
If Sheets(I).Name = "Last" Then
NumberSheets = I - FirstSheet - 1
' add 1 to row number to skip first row
Cells(I - FirstSheet + 1, 1).Value = Sheets(I).Name
Cells(1, 1).Value = ""
' Add the column labels to the top of the sheet
' Replace the values below with the X labels desired
' on the generated chart
Range("B1").FormulaR1C1 = "E4"
Range("C1").FormulaR1C1 = "E5"
Range("D1").FormulaR1C1 = "E6"
Range("E1").FormulaR1C1 = "E7"
Range("F1").FormulaR1C1 = "E8"
' Fill the rows with data copied from the sheets
' I loops through the rows / number of sheets
'  If you add cells to plot, modify the loop below
For I = 1 To NumberSheets
S$ = "=" + Sheets(I + FirstSheet).Name + "!"
Cells(I + 1, 2).Formula = S$ + "E4"
Cells(I + 1, 3).Formula = S$ + "E5"
Cells(I + 1, 4).Formula = S$ + "E6"
Cells(I + 1, 5).Formula = S$ + "E7"
Cells(I + 1, 6).Formula = S$ + "E8"
'  Modify chart type here, this is lines w/ markers
' Look up ChartType in the VBA Object browser for possible values
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Charts").Range("A1:F4"), PlotBy:= _
ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts"
.HasTitle = True
'  Update title as appropriate
.ChartTitle.Characters.Text = "My Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
'  Update x axis title as appropriate
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Cell Reference"
.Axes(xlValue, xlPrimary).HasTitle = True
'  Update y axis title as appropriate
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Value"
ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowNone, LegendKey:=False