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

Question

Hello,

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

Answer 1

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.

Answer 2

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