Hi cqc,

Define a dynamic range. Suppose your data is in the range "A1:G19", then
define a new range name called AcData with the following formula;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

This assumes that column headings are in row 1, and that column A contains a
value for every row in the data range ie no null values or blanks. The range
AcData will expand as you add new rows.

Then in step 2 of the pivot table wizard, enter AcData as the Excel range.

Ed Ferrero
http://edferrero.m6.net/


Every week I run a macro to create a pivot table, using source data whose
table size varies weekly, coded as follows:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!WklyTblSz").CreatePivotTable TableDestination:="", TableName
_
:="PivotTable2"

Before running the macro, I would go to the source data to change the cell
references for the 'WklyTblSz' worksheet, using INSERTNAMEDEFINE.

Is there code I can use so that the references are adjusted depending on
the
last row and column for the weekly input file?

Thanks in advance,
cqc