Macro to update Pivot Table
 

Hi

The name Total refers to a dynamic range, and I use it to make/update
my Pivot Table.

Each month Total expands with one column, and in order to update the
Pivot Table I've to choose
a) PivotTable, Refresh Data
b) PivotTable, Wizard..., Finished - and then add the new column
c) Then add the new column

Is there a way to make a macro that allways reflect the dynamic range
Total for all changes made, and that always sum up the values instead
of count function?

Paul

Macro to update Pivot Table
 

You can define a dynamic range that expands to include new columns and
new rows. For example:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1))

would count the cells used in column A and in row 1, and include those
in the Total range. Use Total as the source for the Pivot Table.

The Count function is the default if there are blank cells in the field,
otherwise Sum is the default function.

Paul wrote:
Hi

The name Total refers to a dynamic range, and I use it to make/update
my Pivot Table.

Each month Total expands with one column, and in order to update the
Pivot Table I've to choose
a) PivotTable, Refresh Data
b) PivotTable, Wizard..., Finished - and then add the new column
c) Then add the new column

Is there a way to make a macro that allways reflect the dynamic range
Total for all changes made, and that always sum up the values instead
of count function?

Paul


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

Macro to update Pivot Table
 

Thx for your reply Debra,

but my problem was not hot to define a dynamic range, but how to
update the PivotTable each time the dynamic range expand by columns.
By now I have to do this manually from PivotTable toolbars:

a) PivotTable, Refresh Data
b) PivotTable, Wizard..., Finished
c) Then add the new column

I'm looking for a macro that could do this automatically.

Paul


Debra Dalgleish wrote in message ...
You can define a dynamic range that expands to include new columns and
new rows. For example:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1))

would count the cells used in column A and in row 1, and include those
in the Total range. Use Total as the source for the Pivot Table.

The Count function is the default if there are blank cells in the field,
otherwise Sum is the default function.

Paul wrote:
Hi

The name Total refers to a dynamic range, and I use it to make/update
my Pivot Table.

Each month Total expands with one column, and in order to update the
Pivot Table I've to choose
a) PivotTable, Refresh Data
b) PivotTable, Wizard..., Finished - and then add the new column
c) Then add the new column

Is there a way to make a macro that allways reflect the dynamic range
Total for all changes made, and that always sum up the values instead
of count function?

Paul