Macro changing dates to American from UK

I have a macro which works perfectly.

However, when I run it it is switching the dates from the original UK format ie 15/03/2014 to American format ie 03/15/2014.

Below is the script -

Sub Copy_To_late()

Dim My_Range As Range, x, i&, dic As Object, MyPath$, foldername$, Ms As Worksheet, FileExtStr$
Application.DisplayAlerts = False
Application.ScreenUpdating = False
MyPath = "P:\geoff allen\"
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If
foldername = MyPath & Format(Now, "yyyy-mm-dd hh-mm-ss") & "\"
MkDir foldername
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1

With Sheets("Sheet1").Range("A1:cr" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
x = .Value
For i = 2 To UBound(x)
If Len(x(i, 1)) Then
If Not dic.Exists(Trim$((x(i, 1)))) Then
dic.Item(Trim$(x(i, 1))) = Empty

.AutoFilter 1, "=" & Replace(Replace(Replace(x(i, 1), "~", "~~"), "*", "~*"), "?", "~?")

Set Ms = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
.SpecialCells(xlCellTypeVisible).Copy

With Ms
.Range("a1").PasteSpecial xlPasteValues
.Range("a1").PasteSpecial xlPasteFormats
.Columns.AutoFit
End With
Application.CutCopyMode = False
On Error Resume Next
Ms.Parent.SaveAs foldername & x(i, 1) & ".csv", FileFormat:=xlCSV

Ms.Parent.Close False
On Error GoTo 0

.AutoFilter Field:=1
End If
End If
Next i
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Really frustrating me as I need this to work ASAP.

Would appreciate any assistance provided.

Thanks,

Geoff

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)