Create a dynamic named range, then use the name in the pivot wizard.
There are instructions he

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

daveydave60 wrote:
Hey Domenic, do you have any good ideas on using an
INDIRECT function to specify a dynamic PivotTable Range
in the PivotWizard Range box. Been struggling with this
for awhile. I figure its impossible. That PivotWizard
range box just doesn't like a "!". It wants to change it
to ".xls!".

some backround? you add rows to the bottom of a range
which you wish to use as a PT range. If its in the same
sheet, the indirect function will work with a simple
refresh of the table. If its in a different sheet, it
fails.

I'm stumped.



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