Wrap Text not retained when Pivot Refreshed

[uncled]

Banging my head on this one! I've got a pivot table (Excel 2003) in which the pivot table options AutoFormat table is not checked and Preserve Formatting is checked. When I open the file and refresh, all formatting seems to be retained with the exception of the Wrap-Text cell formatting. If I reapply the Wrap-Text and refresh, all is ok.

It seems that the Wrap-Text formatting is not retained the first time a pivot table is refreshed after a file is open.

Anyone else experience this and any workarounds? I'm think some code could be created to fix this, but dont' want to head down that road if I'm just having a "moment" ;-)

thanks,
-ue

 

[SkipVought]

Hi,

That is one of the frustrating things about PivotTables.

I would record a macro that does he formatting that you want. Then Post in forum707 to get help in adapting for your PivotTable, as there are specific PivotTable objects that can be used for this requirement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

I used to have that problem but no longer:

On the PivotTable Toolbar use: Select, EnableSelection
If you hover the pointer over a field heading you get a thick black arrow. Click once to select the data and again to select just the headingss.
Then use Format,Cells to apply the desired format.
The format (but not the column widths) then gets remembered upoon refresh.


Gavin

 

[SkipVought]

gavin,

I have never been able to get borders to stick.

Shading, Font, alignment, but not borders.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Well Skip, the formatting (including wrapping) of text in row and column titles and fields is retained for me. In table options I usually have Autoformat Table and Preserve Formatting ticked.
In code (xl2003) my technique records as:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "MTP-5 Service", xlButton, _
        True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

Often, but not always(!) the column widths will be retained when I refresh. Unreliable so tend to have VBA to reset. Also, havung set the formats manually as I described I do sometimes have to select the row containing the titles and autofit it - so the wrapped text displays fully.

Gavin

 

[uncled]

Thanks guys for the responses. However, I'm still grappling with this. Let me restate what I've done. I've created a Pivot table that has a text field that I'm using as Column Header. The Column Header title is pretty long (it's a question) and therefore I've formatted as wrap-text. The data values are also long text strings (i.e. answers). So, I've formatted the Column Header as wrap-text and also the entire column as wrap-text and put Column Width at 25. I've then saved the file.

When I reopen the file and refresh, the wrap-text is not retained, but the column width appears to be, so I have to reapply the wrap-text.

Am I missing something in the posts above or is there a way to loop through file and capture the formatting options, refresh the table, and then loop through again and reapply? It seems I would need an inordinate amount of variable declarations to do this.

thanks again for your help!!
-ue

 

[Gavona]

When I reopen the file and refresh, the wrap-text is not retained, but the column width appears to be, so I have to reapply the wrap-text.

In table options you should still have Preserve Formatting ticked.
Then if you select the field headings as I described I reckon that wrap text will be retained.
However on table refresh Excel does not seem to reliably recognise this and column widths get messed up. That leaves you with needing a routine to (i) reset column widths and (ii) Autofit the heading rows.

Doing this, manually or by macro this is quite simple, though annoying that you need to.
Start by Pivot selecting your column field (hover the pointer until you get that thick arrow) then:
(a) Format, Cells, ...wrap text (this I find is unnecessary but you may as well..
(b) Format, column, width ...
(c) Format, row, Autofit

I shouldn't post code in this forum so please repost in forum707 if you have difficulties.

Gavin

 

[Gavona]

The data values are also long text strings (i.e. answers).

If these are data fields in a pivot table all you can do is count them! Something seems a bit odd here!

Gavin

 

[Gavona]

VBA solution: thread707-1562313

Gavin