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/