Dynamic Ranges Chart VBA

[Shazam]

I need some help. I'm trying to do a dynamic range in a source box in a chart. I have a simple macro recording to create 3 charts every morning.

Is there a code that it will dynamically select a range from cell F20 to the last active cell in that column for the values box of the chart and select a range from cell G20 to the last active cell of that column for the Category Label box? I know there is a named ranges solution but it wouldn’t work out in my situation, long story.

Here is a macro I recorded. Can it be modified to do that?

[vba]Sheets("1st Shift").Select
ActiveSheet.ChartObjects("Chart 37").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Table!R20C7:R21C7" 'F20:f Vaules:
ActiveChart.SeriesCollection(1).Values = "=Table!R20C6:R21C6" 'G20:g Category Labels:
ActiveWindow.Visible = False
Windows("Copy of MS Performance Chartss.xls").Activate
[/vba]

[lucas]

Can you clean up a copy of it and post it? Why do you say a named range solution wouldn't work?

[Justinlabenne]

http://vbaexpress.com/kb/getarticle.php?kb_id=602

[Shazam]

Can you clean up a copy of it and post it? Why do you say a named range solution wouldn't work?

Hi Lucas thank you for replying. The reason I don?t use name ranges in this situation because I get a REF!# errors in the Define Name Box when I copy the worksheet to another workbook. I use a code to consolidate various workbooks into one master workbook and allot of these workbooks contains charts and when using name ranges I'm getting errors in the Define Name Box.

Well I finally figured it out how to get this to work of course I'm not a master in VBA it took me awhile but here?s what I got and it seems to work. I left a small sample file below with a macro named "test". Please let me know if I left something out.

 
 Sub test()
'Choose the worksheet that contains the chart
    Sheets("1st Shift").Select
 
    'Excel names your chart so to be sure do a macro recording
    'and select the chart and stop recording then look at the
    'recording and it will tell you the name of your chart
    ActiveSheet.ChartObjects("Chart 37").Activate
 
    'Define your ranges in the source box of your charts
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Values = Range(Sheets("Table").[F20], Sheets("Table").[F65536].End(xlUp)) ' Values:
    ActiveChart.SeriesCollection(1).XValues = Range(Sheets("Table").[E20], Sheets("Table").[E65536].End(xlUp)) 'Category Lables:
 
    'Deselect your chart
    ActiveWindow.Visible = False
 
    'Name of your workbook
    Windows("Sample Test.xls").Activate
    End Sub

Thank You for the link Justinlabenne.