Pivottable Pivotselect string

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=E9 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.

Hi,
You might consider something like this:

ActiveSheet.PivotTables("PivotTable2").Format xlReport6

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

Mike


>-----Original Message-----
>I have a pivottable which I am attempting to format=20
through VBA.
>
>I use a table driven approach to loop through the fields=20
in the pivot
>table to format various things (borders, numberformat,=20
bold, etc).
>
>I want to be able to use the pivotslect method to be able=20
to format the
>fields, but I'm having little joy. Specifically, I have=20
no problem
>selecting the datafields and their labels, but it's the=20
rowfield totals
>which are killing me. Using the macrorecorder I have the=20
following..
>
>ActiveSheet.PivotTables("Tableau crois=E9=20
dynamique5").PivotSelect _
> "'Centre de Cout'[Tous;Somme]", xlDataAndLabel
>
>I'm afraid it's in French (I'm using French excel). The=20
problem with
>the above is that it does not work when I try to apply it=20
in a
>procedure/ immediate window. Anyone have any thoughts?
>I've been looking through the "standard pivottable report=20
selection"
>documentation and it tells me about spaces and use of=20
single quotation
>marks, but truth be told I was less clear after having=20
read it.
>
>In a perfect world I would like to have a function which=20
converts the
>field name and the mode into "standard pivottable report=20
selection
>format". Anyone have something that can help me out.
>
>.
>

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