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

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Palpha32" wrote:

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

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

"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Palpha32" wrote:

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