Data ranges for Graphs & pivot tables in macros

[Matt E]

I am having trouble generating graphs and pivot tables
with a macro from the correct data. I used the relative
reference feature when recording the macro, but the visual
basic code is always relying on the specific range I used
when it actually creates the new item.

Example (Graph then pivot table):
Range("A2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:C19").Select
Charts.Add

***
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add
(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R4C1:R227C42").CreatePivotTable
TableDestination:="", TableName:= _
"PivotTemp_1"


This works fine this time, but the next time I run the
macro I'll have more data and this won't recognize
anything below the original data block boundaries.

Also, if anybody's looking for extra credit, I'd can't
figue out how to select a range that doesn't include a
summary row at the bottom. In this case, say that A1
contains a Header, A2:C2 contain column labels, A3:C19
contain data (which will expand several rows before the
next time I run the macro), and A20:C20 is a summary row.
I need to find a way to start at A2 and select A2:C19
using relative references.