date in vba when paste defaults to american

[mercmannick]

[VBA]Sub SetDate()
Dim strFileName As String
Set WS = Sheets("ZF")
WS.Range("B:B").Replace What:=".", Replacement:="/", LookAt:=xlPart
End Sub[/VBA]

hi im using this to change Date in excel , when i paste it to my workbook it defaults to american date format is there something im missing ??

and this to copy and paste

[VBA]Set wkbkZF = Workbooks("ZF.xls")
Set wkbkSS = Workbooks("IMF stage starts WK " & CStr(VBAWeekNum(Now(), 1)) & "." _
& CStr(Application.WorksheetFunction.Weekday(Now())) - 1 & ".xls")
Set tgt = wkbkSS.Sheets("Everything").Range("B3")
With wkbkZF.Sheets(1)
Set MyRange = .Range("B5:M" & .Cells(Rows.Count, 2).End(xlUp).Row)
End With
MyRange.Copy Destination:=tgt[/VBA]

Many thanks

Merc

[Simon Lloyd]

try this after your copy and paste [VBA]tgt.value = Format(tgt.value, "dd/mm/yyyy")[/VBA]not tested but should do the trick!

[mercmannick]

Simon

not it is still doing same

05/01/2007 05/02/2007
should be 01/05/2007 & 02/05/2007

Merc

[lucas]

Can't you format your desination range manually...permanantly?

[Simon Lloyd]

Of course you can click column B (on the destination sheet) and then right click, format and format to the required date for the required contry format, but if you really want it in VBA:
[VBA]
Columns("B:B").Select
Selection.NumberFormat = "dd/mm/yyyy;@"
[/VBA]

[mercmannick]

Lucas

No it still dong same , any help would be appreciated

Merc

[lucas]

without reconstructing the workbooks you might try not copying...
instead of this:
[VBA]MyRange.Copy Destination:=tgt [/VBA]
maybe something like this:
[VBA]
tgt = MyRange
[/VBA]

[lucas]

I notice that in SetDate sub you are only replaceing a . with a / what if you format it after you do that?

[Charlize]

[VBA]Sub switch_day_month()
Dim v_date() As String
v_date = Split(ActiveCell.Value, ".")
ActiveCell.Value = v_date(1) & "/" & v_date(0) & "/" & v_date(2)
End Sub[/VBA]