Date format change after merging of multiple workbooks

[yawbus1975]

I'm using the following to merge multiple workbooks into one workbook and it's working ok, except in the merged workbook the any date field formats are being changed to US date. (I'm working in Australia)
Can anyone suggest a fix/reason for this? Thanks in advance!

Sub Merge()
'Merges all files in a folder to a main file.

'Define variables:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

'Allow the user to select multiple workbooks
tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

'Loop through all selected workbooks
For i = 1 To tempFileDialog.SelectedItems.Count

'Open each workbook
Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'Copy each worksheet to the end of the main workbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy after:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet

'Close the source workbook
sourceWorkbook.Close
Next i

End Sub

 

[sijpie]

Dates are a pain in Excel. Often if the first date can be read as an american date, it will put the whole range in that format.

 

[RoryA]

Welcome to the forum.

Is it just the format that is changing, or is it actually altering the dates themselves?

 

[sijpie]

Hi Rory, what I often see happening is that a European date (DD.mm.yy) such as 03.06.20, gets transformed into mm.dd.yy . I am not sure if that is what yawbus is seeing as well.