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/