[RESOLVED] PivotTable Refresh Changes Data Area
I have a pivottable linked to data created via a macro. When I re-run the original macro and the data is reprepared, the refreshed pivottable covers a smaller data range.
The pivottable covers a datarange four columns x 113 rows. When the source is reprepared from my separate macro, even if the data does not change at all, the pivottable updates itself to cover only four columns by anything from seven to 30 rows when I refresh it. The pivottable is set up in Excel, but I have a macro to refresh it:
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
I cannot see why it keeps automatically reducing the datarange in the pivottable. I want the pivottable to have a set, fixed datar ange of four columns by 113 rows, even if the size of the source data preprared from the separate macro reduces (as it may increase again up to 113 rows later and I want the pivottable to still cover all rows). I cannot see why this happens.
Thanks
# 1 Re: [RESOLVED] PivotTable Refresh Changes Data Area
I have also tried rerunning the macro and refreshing the pivottable a number of times and it keeps reducing the number of rows each time, until it reaches two rows and then the pivottable fails.
Any assistance would be greatly appreciated!
Thanks
# 2 Re: [RESOLVED] PivotTable Refresh Changes Data Area
To clarify I am performing the following:
1) Dataset A created from macro A
2) Pivottable created manually, linked dataset A
2) Reran macro A which links data from another dataset and then deletes any blank lines (say X blank lines), to create dataset A.
3) Refreshed the pivottable through the code detailed above in my first posting.
When I do 3, the number of rows that the pivottable is linking to as its source data is reducing by X.
I want to be able to delete lines each time I run the macro, as that is part of preparing a print. However, I would like the pivottable linked to this dataset to retain its datarange, so that it does not reduce by X rows each time the macro is run. Hope that makes sense.
# 3 Re: [RESOLVED] PivotTable Refresh Changes Data Area
If you want to keep pivot table data range unchanged, do not delete (or insert) any row.
Instead of delete a row, just clear it's content.
Another way is if you really need to delete some rows of data table then before refresh the pivot table, just re-set the data range to original range.
anhn at 2007-12-6 11:48:00 >
# 4 Re: [RESOLVED] PivotTable Refresh Changes Data Area
Thanks for that. I have changed the macro so that rather than refreshing the pivottable, it deletes it and then recreates it and that seems to work.
Cheers
