charting problem with activechart.setsourcedata

[Mary Kathryn]

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!

[Jim Cone]

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

[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 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 = "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

[Jim Cone]

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

[Mary Kathryn]

Jim,

Thank you so much for your help. It works!

Mary Kathryn

[paulmilnes]

I just want to thank you for getting me to the last stage of plotting charts with VBA. My line charts were coming out wrong, requiring me to go in and click 'Switch Rows and Columns'. I couldn't see a way to fix this until I saw your parameter 'PlotBy :xlRows'.

Bingo! All solved.

Thanks again.

Paul