Creating an Excel chart that uses data from multiple sheets

Consider the following case:

• I have a single Excel workbook with 4 sheets in it.
• Each sheet represents one quarter of a year.
• Each sheet contains 2 columns:
  • Date
  • Revenue

I now want to create a line graph for daily revenue over the whole year.

The X-axis should start at January 1st (first row of the first column of the first s开发者_开发技巧heet) and end at December 31st (last row of the first column of the fourth sheet).

There should be a single line plotting the daily sales over that year on the Y-axis.

I think the most straight forward thing to do is to add a fifth sheet to your workbook that references the cells from the other 4 sheets. Then create your graph from the data on the fifth sheet.

Also, I think you will get better results with an XY Scatter plot, if you use a line chart, the X axis is pre-defined but in your case, you want your X axis to be the date.

Here's a subroutine to get you started automating adding a chart

Sub zx()
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim Chrt As Chart
    Dim Srs As Series

    Set wb = ActiveWorkbook
    Set Chrt = wb.Charts.Add(After:=wb.Worksheets(wb.Worksheets.Count))
    Chrt.ChartType = xlXYScatterLines
    Chrt.SeriesCollection(1).Delete
    Chrt.Name = "Annual Trend"

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q1"
    Set sh = wb.Sheets("Quarter1")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q2"
    Set sh = wb.Sheets("Quarter2")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q3"
    Set sh = wb.Sheets("Quarter3")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address

    Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "Q4"
    Set sh = wb.Sheets("Quarter4")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address
End Sub