I have a macro i use to copy data from one file to another the code is shown below. The data contains a column with the date in the format of dd/mm/yyyy, when i copy and paste the data to the new sheet the date is changed to mm/dd/yyyy, does anyone know why this is happening and any ideas for resolving it?
Code:
Sub CopyNewDate()
Dim wbSource As Workbook, wbTarget As Workbook
Dim rCopy As Range
Dim tPath As String
Dim RangeSource As Range
Dim Filldata As Range
Dim X As Variant
Dim LastRow As Long
'***************************************************************************************
' Copy Range from A5 to next blank from source data in selected file
'***************************************************************************************
Application.DisplayAlerts = False
With Application
.ScreenUpdating = False
On Error Resume Next
' open the source workbook as ReadOnly
Set wbSource = Workbooks.Open("C:\DATA\Example.xls", False, True)
LastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
wbSource.Worksheets("Example").Range("A5", "O" & LastRow).Copy
wbSource.Close False ' close the source workbook without saving changes
Application.GoTo Worksheets("DATA").Range("C65536").End(xlUp).Offset(1, 0), True
ActiveSheet.PasteSpecial
Set wbSource = Nothing
Set wbTarget = Nothing
Set rCopy = Nothing
.ScreenUpdating = True
.CutCopyMode = False 'clear Clipboard
End With
'***************************************************************************************
' Unmerge all cells and fill data down
'***************************************************************************************
Sheets("DATA").Select ' Selects range to fill data down
Set Filldata = Range(E1, Cells(Rows.Count, 5).End(xlUp))
Filldata.Select
Filldata.UnMerge
For Each X In Selection.Cells
If X.Text = "" Then
X.Value = X.Offset(-1, 0).Value
End If
Next X ' IF statement fills each cell until new value then fills that value for entire range.
End Sub