I'm not following you. When the user change language (it actually is
more languages than just the two examples) tha pivots' source,
Table1's, heads are updated by code and also one of the columns (with
a fixed validating choice list also changing language) cells' values
are replaced with the new languages' words.

Do you mean I should have columns hidden for every language and always
run code on cells' changes to update the value in the associated cells
for the other languages.?

regards
Tskogstrom
-------------------------------------------------------

On May 26, 2:12*pm, "Patrick Molloy"
wrote:
surely you only need one source table?
if you change, in the table, all "Yes" *values to "Ja" and "No" values to
"Nej" ... when you refresh the pivot table, this will pick up the chages
you then don't need the "Eng" or "Swe" prefixes

alternatively keep one table, but add another column for the other language

Yes Ja 234
No *Nej 234

...

"tskogstrom" wrote in message

...

Hi,
I have a Table "Table1" as data source to two pivots on one sheet.

I switch my file/application between different languages - the heads
in the table are still meaning the same and also the pivots should
still be looking in the same way.

With VBA, I change the value in the table's head cells, and also
changes the content in one column (2) where the filered word "yes"
will be "Ja" in swedish etc. So it is both the tables head and some
content that is changed.

The versions table "Table1" the pivots are based on:

- English Table version:
Eng_Col1 Eng_Col2 Eng_Col3
1 Yes 234
2 No 234
3 Yes 234
4 Yes 3242
5 No 435

- Swedish Table version:
Swe_Kol1 Swe_Kol2 Swe_Kol3
1 Ja 234
2 Nej 234
3 Ja 234
4 Ja 3242
5 Nej 435

One Pivot use "Yes" as Report Filter and another use "No".
This means after I change to swedish language in the table, the first
pivot uses "Ja" and the other uses "Nej".

I have the Yes/Ja and No/Nej as named ranges:

Yes: "Range_Eng_Yes
Ja: "Range_Swe_Yes"

No: "Range_Eng_No"
Nej: "Range_Swe_No"
----

- English Pivot results after needed macro:

Eng_Col2 (Yes)

Sum of Eng_Col3
Eng_Col2 Total
1 234
2 234
3 234
4 3242
5 435
Grand Total 4379

- Swedish Pivot results after needed macro

Swe_Kol2 (Ja)

Sum of Swe_Kol3
Swe_Kol2 Total
1 234
2 234
3 234
4 3242
5 435
Grand Total 4379
------

Cay you help me out with this? It should be the same fields hidden and
visible in both languages. The thing that makes it a bit more
complicated is the fact the filtered data in the pivot's "Report
filter" also changes.

Many thanks in advance

/Tskogstrom