Mary Kathryn,
Even after ten years at this, I still feel that Microsoft was playing some kind
of cruel joke when they set up the nomenclature for charts...
There are two distinct types of charts in Excel:
1. One is a chart consisting of an entire sheet (a chart sheet).
A chart sheet is not a worksheet.
Charts(1) is the first chart sheet in the workbook.
2. The other is a chart placed on top of a worksheet - "embedded" on the worksheet.
The worksheet chart has a container around it called a "Chart Object".
Therefore, you have to identify the chart object before you can specify the
particular chart you want to refer to...ActiveSheet.ChartObjects(1).Chart.
If you want to create a separate chart sheet then your recorded code could be
modified to retain the original sheet as the source for the chart data.
When you add a chart sheet it becomes the active sheet. So...
Sub Macro2()
Dim StartSheet As Excel.Worksheet
Set StartSheet = ActiveSheet
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=StartSheet.Range( _
'rest of code remains the same.
'-----------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Mary Kathryn" wrote in message
Thanks for responding. I am new at Excel VBA, so please bear with me!
I am trying to create a chart on another sheet, but it could be created
on the active sheet. I would like the users to run a macro to create
this chart. There will be 10 or more worksheets, so I need to create
the chart based on the selected worksheet. I am selecting the
worksheet, then running the macro.
Thanks for you help!
Here is the code the macro creates:
Sub Macro2()
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"E18:CA18,E29:CA29,E40:CA40,E52:CA52"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).Name = "=""Self"""
ActiveChart.SeriesCollection(2).Name = "=""Other"""
ActiveChart.SeriesCollection(3).Name = "=""Community"""
ActiveChart.SeriesCollection(4).Name = "=""Integration"""
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "IAM Rating"
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Week"
Axes(xlValue, xlPrimary).HasTitle = True
Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "AveragePercentage"
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
MinimumScaleIsAuto = True
MaximumScale = 1
MinorUnitIsAuto = True
MajorUnit = 0.05
Crosses = xlAutomatic
ReversePlotOrder = False
ScaleType = xlLinear
DisplayUnit = xlNone
End With
End Sub
Mary Kathryn
