Creating a chart from a named range

[GFAlexander]

I am writing a macro for Excel and would like to be able to create a chart each time the macro is run for a range of data that will be different each time the macro is run.

I can select the data without directly referencing the cells by selecting a start and finish point as follows:

Selection.Find(What:="Total", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

However if I continue and create a chart (insert, chart, etc) the data range is specified by cell references as follows:

=EPROSS!$F$99:$J$273

My data will be in different cells each time the macro is run because the data I want to use is collapsed sub-totals and there will be different amounts of data each time.

Is there a way that I can achieve this?

Thanx in advance.

Gary

 

[xlbo]

For:
=EPROSS!$F$99:$J$273
you can build a string:

myVar = Activesheet.name & "!" & selection.address

where you have run the code that you posted to select the data area

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question