Dynamic Range Chart Macro Question

  • Thread starter Thread starter GerbilGod7
  • Start date Start date
G

GerbilGod7

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
 
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)
 
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
 
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.
 
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
 
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.
 
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.
 
Back
Top