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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
(e-mail address removed)
 
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
 
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
 
Back
Top