Barb,
Thanx for the reply.

My pivot table actually starts at d7 [there is some titling/etc from other
parts of the workbook], and ends at column x.
So I modified your formula as follows, but it doesnt' work ...

=OFFSET(acc!$D$7,0,0,COUNTA(acc!$D$7:$D$2000),COUN TA(acc!$4:$24))

*** Tried your solution with everything starting in d1, it does work.

Regards,
-Mike

"Barb Reinhardt" wrote:

I set up a dynamic range for pivot table sources that expands/contracts as
needed.

Let's say row 1 has a header and the data is in subsequent rows (with no
empty rows).

Define the range this way

=Offset(Sheet1!A1,1,0,counta(Sheet1!A:A)-1,counta(sheet1!1:1))

And set the source to the defined range.

HTH,
Barb Reinhardt

"MikeF" wrote:


Have a nicely-constructed Pivot Table that runs off Sheet ACC / Range
d7:x2000 every week.

There are numerous weekly files [well, 52 per year!].
The sheet and field names are exactly the same.
... Just the data is different each week.

Have attempted numerous methods of copying/copy worksheet/and so on into
each week, then using Pivot Table / Options / Change Data Source, but to no
avail.

Any assistance will be sincerely appreciated.
- Mike