Want to plot a graph using data from 2 different worksheets in sam

workbook.

I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in the
same file with different worksheet, I cant seem to plot it.

I had to copy all the data on same worksheet and then plot it. But there
must be a way to do it without this action.
[email protected]

Try charting one year then: Chart; Source Data; Add to add the rest.


"cteq" <[email protected]> wrote in message
news:[email protected]...
> workbook.
>
> I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in
> the
> same file with different worksheet, I cant seem to plot it.
>
> I had to copy all the data on same worksheet and then plot it. But there
> must be a way to do it without this action.
> [email protected]

I can do one year no problem at all.

"Steve" wrote:

> Try charting one year then: Chart; Source Data; Add to add the rest.
>
>
> "cteq" <[email protected]> wrote in message
> news:[email protected]...
> > workbook.
> >
> > I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in
> > the
> > same file with different worksheet, I cant seem to plot it.
> >
> > I had to copy all the data on same worksheet and then plot it. But there
> > must be a way to do it without this action.
> > [email protected]
>
>
>

Try some code:

'before you start, create a blank chart sheet in your workbook


Option Base 1
Sub addSeriesFromAllSheets()

Charts("chart1").Activate ' rename chart1 to match the name of your new,
blank chart
'ensure the chart is blank
RemoveData

For Each ws In Worksheets
If IsNumeric(Right(ws.Name, 4)) Then 'use a sheet naming convention
'so you miss those you don't wish to chart.
'in this case, if the last four characters are numbers, it data will plot on
the chart
ActiveChart.SeriesCollection.Add _
Source:=ws.Range("A1:B6") ' Edit this so it covers the range in
the sheets you wish to chart
End If
Next ws
End Sub
Sub RemoveData()
On Error GoTo Exit_RemoveData
While ActiveChart.SeriesCollection.Count > 0

ActiveChart.SeriesCollection(1).Select
Selection.Delete
Wend
Exit_RemoveData:
End Sub

"cteq" <[email protected]> wrote in message
news:[email protected]...
>I can do one year no problem at all.
>
> "Steve" wrote:
>
>> Try charting one year then: Chart; Source Data; Add to add the rest.
>>
>>
>> "cteq" <[email protected]> wrote in message
>> news:[email protected]...
>> > workbook.
>> >
>> > I want to take 2003, 2004 sales and 2005 sales on same graph. Data is
>> > in
>> > the
>> > same file with different worksheet, I cant seem to plot it.
>> >
>> > I had to copy all the data on same worksheet and then plot it. But
>> > there
>> > must be a way to do it without this action.
>> > [email protected]
>>
>>
>>

Name your ranges (Sales2002, Sales2003, etc), then add them to the chart
preceeded by the file name...

eg

Series 1 Values = Sales.xls!2002Sales
Series 2 Values = Sales.xls!2003Sales
etc...

will do the trick...

Hth,
Oli

--
Message posted via http://www.officekb.com

This page might help:

http://peltiertech.com/Excel/ChartsH...iffSheets.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

cteq wrote:

> workbook.
>
> I want to take 2003, 2004 sales and 2005 sales on same graph. Data is in the
> same file with different worksheet, I cant seem to plot it.
>
> I had to copy all the data on same worksheet and then plot it. But there
> must be a way to do it without this action.
> [email protected]