How do I create a VBA macro that will graph a changing range?

Currently, I have a table that will automatically update when new data is added to a spreadsheet. I am able to easily create a macro that will graph a set range, but how do I get it to automatically update the range so that it graphs all the right data? My goal is to be able to create a button that I can press at any time that will run a macro on this table and graph the results.

Right now, my code is:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/24/2010 by Nicole
'

''
    Range("R1:S12").Select
    Range("S12").Activate
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Intakes").Range("R1:S12"),PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Intakes"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "# Cases that day"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

Thanks,

Nicole开发者_StackOverflow中文版

All you need to do differently (though it slightly depends on how the original cell gets its value updated) is to put your Macro in a Worksheet Change Event, then compare the Target to the Range of Interest:

Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("R1:S12")) Is Nothing Then
    Range("R1:S12").Select
    Range("S12").Activate
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Intakes").Range("R1:S12"),PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Intakes"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "# Cases that day"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End If  

End Sub

FYI, found another solution for this...

I was struggling with the same problem:

I had already created a dynamic name that selects my desired graph range [the whole range], but after I inserted this name into the graph range, it immediately converted back to a static range [cell reference] and no longer graphed my dynamic range name.

I created a macro: select graph, change data selection, overwrite static cell range with my created dynamic name, enter.

I assigned this macro to a "Update Graph" button, but noticed the macro had taken the name's range as static, not dynamic.

SO HERE'S THE SOLUTION: open macro menu, modify created macro... in the macro, look for the fixed cell range, overwrite it with 'sheetname'!dynamicname

done. button should now update graph with dynamic range data.

hope this helps someone out there. cheers.

A reasonably simple solution is to make that range a named range, using the input box that is located to the right of the formula input section. That way, instead of referring to the range as "R1:S12", you can access it by name, Range("MyOwnRange"). If you insert rows or columns inside the named range, it will resize automatically to include the new rows.