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")
....