Within the code module, we want to create an event macro.To do so, choose Worksheet in the Object drop-down box on the left.
For instance I created a pivot table and a chart, using the Display Language = English, only to find out that if German was selected on another machine, the datasource wouldn't update anymore.
I cannot find any way to make the datasource language independent. If the filed is called Collumn1 (which would be the default name of a field in EN) well, in German that is... Which of course prevents the chart to be updated, even when reading the same datasource!
Aleksandrs asked a great question on the You Tube video comments.
If your pivot table and source data are on the same sheet then you will need to add code to disable events.
Note: The Selection Change event that is added by default will run any time the user selects a cell in the sheet.
Since we only want the code to run when the user edits/changes cells , we use the Change event.
Checkout my article on VBA Code Modules & How to Run Macros Based on User Events to learn more about the sheet modules and events.
Next, just below the Worksheet_Change line, type in this instruction: The Refresh All method will refresh all the pivot tables, queries, and data connections in the workbook.
two excel files, one for German settings, the other for Ehglish.
With pivot tables, it's often the little things that are frustrating..doesn't show up when you refresh, number formatting goes missing, fields have weird names...things like that.
Renamed fields are not preserved when switching from a Display Language to another.