In your macro, you could use a dynamic range as the pivot table source,
and it would automatically expand to include new data. There are
instructions he

http://www.contextures.com/xlPivot01.html

Tyrone Lopez wrote:
Help! I created a simple macro to launch a pivot table covering data
with fields as follows:

Date Units Amount

I created a pivot table manually and the results was correct. Created a
macro with the same instructions, tested it and it worked. However later
with a longer list of data the macro did not provide a correct answer as
compared to a manually created pivot table.

Any clue as to the problem? Does a pivot table created through a macro
'retain' the data range and therefore gives an incorrect result if the
data range is longer than the original?




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html