Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Update | Excel Discussion (Misc queries) | |||
Pivot Table Update | Excel Discussion (Misc queries) | |||
record a macro to update pivot table | Excel Discussion (Misc queries) | |||
Pivot Table update | Excel Discussion (Misc queries) | |||
Give File Time to update Pivot Table when started bij Macro | Excel Discussion (Misc queries) |