I'm trying to use a macro to define dynamic ranges for all of th
spreadsheets in a workbook, and then chart the data that is referred t
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 eac
worksheet, with names like "(worksheet name)-EjectaX". I think I messe
up the naming conventions, but I can't find a definition for th
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 eac
worksheet that refer to the range in each worksheet?

Thanks

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

Haven't tested this, but your syntax is wrong, so give this a try

Activeworkbook.Names.Add Name:= ws.Name & "-EjectaX"), _
RefersTo:= "= OFFSET('" & ws.Name & "'!$J$1,'"
& ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

Activeworkbook.Names.Add Name:= ws.Name & "-RampartX"), _
RefersTo:= "= OFFSET('" & ws.Name &
"'!$J$1,'" & ws.Name & "'$S$7,0,'" & ws.Name & "'!$S$6-'" & ws.Name &
"'!$S$7,1)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"GerbilGod7 " wrote in 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/

I tried this:

ActiveWorkbook.Names.Add Name = (ws.Name & "EjectaX")
RefersTo:="OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'$S$2,0,'"
ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

Hoping that it would create a named range "(Worksheet)EjectaX" but
get a "Name is not valid" error when I try to run the macro

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

A sheet level name is of the form Data!EjectaX

so

ActiveWorkbook.Names.Add Name = ws.Name & "!EjectaX",

But i would use

ws.Names.Add Name:="EjectaX",


there is no reason for the parentheses in either case.

--
Regards,
Tom Ogilvy

"GerbilGod7 " wrote in message
...
I tried this:

ActiveWorkbook.Names.Add Name = (ws.Name & "EjectaX"),
RefersTo:="OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'$S$2,0,'" &
ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

Hoping that it would create a named range "(Worksheet)EjectaX" but I
get a "Name is not valid" error when I try to run the macro.


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

After some tweaking, I've gotten it to work, sorta... Here's what
use:

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Nam
& "'!$J$1,'" & ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name
"'!$S$2,1)"

In each worksheet, it creates a named range with this name:

EjectaX ...[bunch of spaces]... (worksheet name)

I'm not sure why it's doing this; I've tried:

"'" & ws.Name & "'!EjectaX" etc, but it doesn't make a difference.

How do I get it to name these ranges properly, and ultimately grap
them

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

This worked fine for me:

Sub Tester1()
For Each ws In Worksheets
ws.Names.Add Name:=ws.Name & "!EjectaX", _
RefersTo:="=OFFSET('" & ws.Name & "'!$J$1,'" & _
ws.Name & "'!$S$2,0,'" & ws.Name & "'!$S$7-'" _
& ws.Name & "'!$S$2,1)"
Next
End Sub


You need an equal sign in front of the Offset or your formula gets turned
into a text string. You were also missing a ! before $S$2

the name is Sheet1!EjectaX (as an example).

It is shown with the sheet name on the right (as you describe - but that is
the normal way to show it), but the name is as the above.

--
Regards,
Tom Ogilvy


"GerbilGod7 " wrote in message
...
After some tweaking, I've gotten it to work, sorta... Here's what I
use:

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Name
& "'!$J$1,'" & ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name &
"'!$S$2,1)"

In each worksheet, it creates a named range with this name:

EjectaX ...[bunch of spaces]... (worksheet name)

I'm not sure why it's doing this; I've tried:

"'" & ws.Name & "'!EjectaX" etc, but it doesn't make a difference.

How do I get it to name these ranges properly, and ultimately graph
them?


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

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/

.