Source Data in Pivot Tables

[Guest]

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:=xlDatabase, 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 INSERT>NAME>DEFINE.

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

 

[Ed Ferrero]

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/

 

[Guest]

What about if i want to a new column?

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/

 

[Roger Govier]

Hi Linda

Assuming you only have headers for the columns you want to use in row of the
sheet, then modify Ed's formula to

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

Alternatively, for a non-volatile formula
=Sheet1!$A$1:INDEX(Sheet1!$1:$65536,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

 

[Guest]

Thanks Roger.

If i'm not using pivot table,then how?My SourceData should be expand as i
add new rows or new columns or both and it will update the chart.i've tried
to use the offset function but its just work for adding new row.Adding a new
column does not effect the chart created.i use OFFSET with a defined name.

date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

 

[Roger Govier]

Hi Linda

date-refers to:=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
sales-refers to:=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)

Each of these ranges are only 1 column wide, so there would be no question
of adding a column.
A new column would have to have a new Name inserted, along with the
appropriate formula.

If you want your WklyTblSz table to grow automatically as you add rows OR
columns, then use
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),COUNTA($1:$1))

After refreshing the PT, the new column would appear as new field in the
field list.
In order for any new added column to show in your PT, you would need to drag
that new field to the position required within the PT.

 

[Guest]

Roger,
is it a need for me to use Pivot Table?if i just create chart using chart
wizard,is it still possible for me to use the OFFSET that you have suggest?
sorry cause my knowledge in excel is quite bad.

thanks~