I don't know if this will help any but I've created
dynamic rages without the use of VBA, I just used defined
names. If you go to http://www.bmsltd.ie/Excel/ it is
Stephen Bullens site on excel. He has a Graph that
dynamically can scroll and zoom on data based on where a
defined name is initially set. There is a list of files
and descriptions on his site the file that demonstrates it
is called FunChrt7.zip. Check it out, it can be
manipulated to have multiple dynamic ranges and multiple
dynamic X-Axis Category labels.
-----Original Message-----
I'm trying to use a macro to define dynamic ranges for
all of the
spreadsheets in a workbook, and then chart the data that
is referred to
in those ranges. Here is what I have so far, but it isn't
working:



Sub DynamicGraph()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Range(ws.Name & "-EjectaX").Name = "OFFSET('" &
ws.Name & "'!$J$1,'"
& ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name
& "'!$S$2,1)"
ws.Range(ws.Name & "-RampartX").Name = "OFFSET('" &
ws.Name &
"'!$J$1,'" & ws.Name & "'$S$7,0,'" & ws.Name & "'!$S$6-'"
& ws.Name &
"'!$S$7,1)"

End Sub



The idea was that it would create two new named dynamic
ranges for each
worksheet, with names like "(worksheet name)-EjectaX". I
think I messed
up the naming conventions, but I can't find a definition
for the
parameter. The Y values for each graph refer directly to
the X values,
so that's easy enough to do.

Once I get this macro to work, how do I get it to create
graphs in each
worksheet that refer to the range in each worksheet?

Thanks!


---
Message posted from http://www.ExcelForum.com/

.