VB date formats - UK / US - HELP!!!!!

[domfrance]

Hello,

I defined Pivot Tables and grouped items by Months/Days.

My local settings are UK - IE, that means that the format for the date in Excel are considered in UK style ( dd/mm/yy or in the case of a group Months/Days dd - MMM).

Then, I Activated my worksheets with VB, so everytime I am selecting the sheets, the Pivot Table are automatically refreshing. Now, my Pivot Report grouped by Months/Days is showing a different format: d - mmm ( example : 2 - Feb )....and that is US Format date...

This is the macro I use :
Private Sub Worksheet_Activate()
Sheets("Pivot").PivotTables("PivotTable1").RefreshTable
End Sub Problem = The dates are not anymore sorted correctly because of the Date Format.

Analyse = That's only happenning when VB macro is activated

Conclusion = My understanding is that VB is not taking the local Dates Format settings, and doing so, my pivot table is sorted this way :

10-Feb 11-Feb 12-Feb 13-Feb 14-Feb 15-Feb 16-Feb 17-Feb 18-Feb 19-Feb 20-Feb 21-Feb 22-Feb 2-Feb 3-Feb 4-Feb 5-Feb 6-Feb 7-Feb 9-Feb

Question = How Can I do to keep the Worksheet AutoRefresh and have these result :

02-Feb 03-Feb 04-Feb 05-Feb 06-Feb 07-Feb 09-Feb 10-Feb 11-Feb 12-Feb 13-Feb 14-Feb 15-Feb 16-Feb 17-Feb 18-Feb 19-Feb 20-Feb 21-Feb 22-Feb
Please Help me

[Simon Lloyd]

Rather than sheet activate would this be better?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
PivotTable = Format(PivotTable, "dd mm yyyy")'''''i added this line i don't
'''know anything about pivot tables or how to format them
End Sub
Ot maybe change the word PivotTabel before the "=" to Target and the word after "(" to the same, like i said i have no knowledge of Pivot Tables.
Regards,
Simon