macro to create charts in worksheets with arbitrary names

Jon,

I truly appreciate your reply to my cry of despair ;-).

I've tried your code and see a chart appear with a single bar at X=1 and Y=10, but subsequently the macro chokes on the 'Define the data block'

ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R1000C8"

with a run-time error message 1004
'Unable to set the Values property of the Series class'

What went wrong? I use Excel 2002 10.2614.2625 under Win2KPro with the latest updates.

z.entropic

"Jon Peltier" wrote:

Sorry, Z, it wasn't so long ago I struggled with this stuff, I should
try to remember what it was like.

I'll start with the code you posted halfway up this thread. We'll define
variables for the sheet name and the range. The range you selected
before running the wizard in the macro recorder has no relation to the
ranges used later, so we can leave it out.

Your Y values for both series are in column H, and the X values are in
columns I and J. Series names are in row 1, data in rows 2 through 1000.

Sub MakeAChart()
'' declare variables for active sheet name and selected range
Dim sSheet As String
Dim rRange As Range
sSheet = ActiveSheet.Name

'' add chart and put it where you want it
Charts.Add
ActiveChart.Location Whe=xlLocationAsObject, Name:=sSheet

'' Add first series (sometimes charts.add does, sometimes not)
If ActiveChart.SeriesCollection.Count = 0 Then
ActiveChart.SeriesCollection.NewSeries
End If

'' Define the data and type
ActiveChart.SeriesCollection(1).Values = _
"=" & sSheet & "!R2C8:R1000C8"
ActiveChart.SeriesCollection(1).XValues = _
"=" & sSheet & "!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=" & sSheet & "!R1C9"
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

'' Add second series and define data
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = _
"=" & sSheet & "!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).XValues = _
"=" & sSheet & "!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Name = "=" & sSheet & "!R1C10"
End Sub

This procedure was tested and works just fine. I might make additional
adjustments, but I follow the engineer's creed: If it works, you haven't
messed with it enough.

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

z.entropic wrote:

OK, let me explain mine and others' problem: your answer is it's like
getting a book in a foreign language with one sentence translated...
For you guys fluent in VB, mine and others questions may seem
ridiculous and we may seem dumb, but since I see a macro code once in
a quarter and don't know any of these German-looking VB statements
with 5 terms joined together, I get stumped by incomplete answers.
You yourself quickly added three additional points to your original
reply...

I and, I trust, many other readers/posters do appreciate your time
and effort in helping us guide through the VBA jungle, but many of
your and others' off-side comments convince me it would be a waste of
my time to try to learn VBA for the few things I need it. In the
end, I have to rely on your and others' goodwill, time and effort
which, hopefully, is a free ad for your business when more serious
problems must be solved.

Finally, both Jon's and your proposed solutions to my original
problem might seem trivial to you, pros, but after trying many
versions of the modified macro, I still don't know how to
write/create a simple chart-creation macro working in a worksheet
with an arbitrary name. So, here is a simples chart-making
macro--how should it be modified using your or Jon's ideas to make it
independent of the "Sheet1" name?

Regards and thanks,

z.entropic

"Tushar Mehta" wrote:


I gave you a specific example of how to work around the hardcoded
names. Where are you lost?

Three additional points.

First, while the XL macro recorder sets the charttype first and the
sourcedata second, you need to reverse those steps in your macro.

Second, to get strings for the Values and XValues properties, use
something like

....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8"

Third, to create a chart that refers to only those cells that
contain data, use something like:

with SrcSheet ....Values= "='" & .name & "'!" _ &
.range(.cells(2,8),.cells(2,8).end(xldown))) _ .address(false,
false, xlr1c1)

Alternatively, use named formulas. See the Dynamic Charts page of
my web site for more on this methodology.

-- Regards,

Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA
add-ins, tutorials Custom MS Office productivity solutions

In article ,
says...

Now you both lost me--how do I use your code if my recorded macro
is full of references to the specific name of the renamed active
worksheet:

' Keyboard Shortcut: Ctrl+a ' Range("H2:H10").Select Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"),
PlotBy:= _ xlColumns ActiveChart.SeriesCollection(1).Values =
"=Data!R2C8:R1000C8" ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).Name = "=Data!R1C10"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Data" With
ActiveChart

z.entropic


"Jon Peltier" wrote:


Missed that. Of course, the other solution is to use

ActiveSheet.ChartObjects.Add(L, T, W, H).Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11")

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

Tushar Mehta wrote:


The Charts.Add creates a new chart in its own sheet.
ActiveSheet at that point refers to the newly minted
chartsheet!

Use something like the untested:

dim SrcSheet as worksheet, aChart as chart set
srcsheet=activesheet set achart=charts.add
achart.SetSourceData Source:=srcsheet.Range("G2:H11") ....