Macro to update Pivot Table

[Paul]

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]

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(Data!$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]

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