Pivot table and row heights

[Palpha32]

I have a worksheet that contains seven pivot tables. I have two questions -
1. How to keep the formats when the pivot tables are refreshed. I have
tried all of the options but no luck
2. How to refresh the tables on exit or closing the sheet
Thanks
Peter

 

[Shane Devenshire]

Hi,

Depends on the version of Excel you are using. Also depends on what type of
formatting you are trying to retain when refreshing.

For the sake of this answer lets assume that row height and column width are
your only concern. In 2003, while the pivot table is selected choose Pivot
Table, Table Options, uncheck AutoFormat Table. This option applies only to
the active pivot table.

There is no built in feature to refresh before save or close, but there is a
Refresh on Open option. Choose Pivot Table, Table Options, and check Refresh
on Open.

If you really needed a refresh on close or save you could do it with VBA but
I think that Refresh on Open covers you better, because macros can be
disabled.

 

[Palpha32]

Thanks for your reply Shane. I should have been clearer in my question. The
excel version is 2007. I have a macro which refreshes the pivot tables
before carrying out further operations. When the refresh happens the row
heights change. This is not desirable because other cell become too small.
Peter

 

[Roh]

I am facing the same issue on Excel 2007 where the row height changes on refresh. This is despite the fact that the "Preserve cell formatting on update" option is enabled. Please share if anyone has a workaround. Thanks!

Roh

 

[gjc]

I found a solution for the pivot table row height. It is cumbersome but works.

On a blank column I entered some words (i.e., "Data used for row ht"), wrap the text, and format the column width to result in the height that you want. When I refresh the pivot table it keeps the row height.

 

[garbageData]

gjc- thanks for the tip! simple and effective!!

I found a solution for the pivot table row height. It is cumbersome but works.

On a blank column I entered some words (i.e., "Data used for row ht"), wrap the text, and format the column width to result in the height that you want. When I refresh the pivot table it keeps the row height.