Pivottable Pivotselect string

[mo_in_france]

I have a pivottable which I am attempting to format through VBA.

I use a table driven approach to loop through the fields in the pivot
table to format various things (borders, numberformat, bold, etc).

I want to be able to use the pivotslect method to be able to format the
fields, but I'm having little joy. Specifically, I have no problem
selecting the datafields and their labels, but it's the rowfield totals
which are killing me. Using the macrorecorder I have the following..

ActiveSheet.PivotTables("Tableau croisé dynamique5").PivotSelect _
"'Centre de Cout'[Tous;Somme]", xlDataAndLabel

I'm afraid it's in French (I'm using French excel). The problem with
the above is that it does not work when I try to apply it in a
procedure/ immediate window. Anyone have any thoughts?
I've been looking through the "standard pivottable report selection"
documentation and it tells me about spaces and use of single quotation
marks, but truth be told I was less clear after having read it.

In a perfect world I would like to have a function which converts the
field name and the mode into "standard pivottable report selection
format". Anyone have something that can help me out.

 

[Mike215]

Hi,
You might consider something like this:

ActiveSheet.PivotTables("PivotTable2").Format xlReport6

xlReport6 is a constant associated with a table format
(Auto Format). Examples of the formats can be seen by
righ clicking anywhere in the Pivot table and looking at
format report. Using these special formats allows you to
flip the pivot table around without loosing the formating
you've done. So, you can pick any color scheme and
formatting you like and set the format with code then use
a bit more code to layout the pivot table as you like

Mike

 

[mo_in_france]

Mike

That's excellent. I'm really kicking myself that I've been wastng so
much time on formatting when there exists this property which will do
90% of the formatting that I require... and I'll never have to worry
about trying to get those standard pivot strings again.

Thanks again

Mohsen