How to create a VBA macro that will display the range of change?

Question

I currently have a spreadsheet that will automatically update when new data is added to the spreadsheet. I can easily create a macro that will display a range of values, but how can I make it automatically update the range so that it displays all the data I need? My goal is to create a button that I can click at any time that will run a macro in this table and display the results.

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

Thank,

Nicole

Answer 1

, - ( , ) , , Target :

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

Answer 2

FYI, ...

:

, [ ], , , [ ] .

: , , , .

" ", , , .

: , ... , , " "! dynamicname

. .

, - . .

Answer 3

, , , . , "R1: S12" "" ( "MyOwnRange" ). , , .