I am stumped! How do I make the following generic, so that it chooses
the active sheet and not "Sheet1"?

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"E18:CA18,E29:CA29,E40:CA40,E55:CA55"), PlotBy:=xlRows


I tried :

ActiveChart.SetSourceData Source:=Activesheet.Range( _
"E18:CA18,E29:CA29,E40:CA40,E55:CA55"), PlotBy:=xlRows

but get "object required".

I sure would appreciate any help! Thanks!


--
Mary Kathryn
------------------------------------------------------------------------
Mary Kathryn's Profile: http://www.excelforum.com/member.php...o&userid=31905
View this thread: http://www.excelforum.com/showthread...hreadid=516320

Mary Kathryn,

It worked for me.
Is your active sheet a worksheet with a chart on it? (not a chart sheet)
Is the chart selected?

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Mary Kathryn"
wrote in message...

I am stumped! How do I make the following generic, so that it chooses
the active sheet and not "Sheet1"?

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"E18:CA18,E29:CA29,E40:CA40,E55:CA55"), PlotBy:=xlRows
I tried :

ActiveChart.SetSourceData Source:=Activesheet.Range( _
"E18:CA18,E29:CA29,E40:CA40,E55:CA55"), PlotBy:=xlRows

but get "object required".
I sure would appreciate any help! Thanks!
Mary Kathryn

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 Whe=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 = "Average
Percentage"

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
------------------------------------------------------------------------
Mary Kathryn's Profile: http://www.excelforum.com/member.php...o&userid=31905
View this thread: http://www.excelforum.com/showthread...hreadid=516320

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 Whe=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

Jim,

Thank you so much for your help. It works! :)

Mary Kathryn


--
Mary Kathryn
------------------------------------------------------------------------
Mary Kathryn's Profile: http://www.excelforum.com/member.php...o&userid=31905
View this thread: http://www.excelforum.com/showthread...hreadid=516320