domfrance
02-22-2007, 02:07 PM
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
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