PDA

View Full Version : date in vba when paste defaults to american



mercmannick
05-22-2007, 10:51 AM
Sub SetDate()
Dim strFileName As String
Set WS = Sheets("ZF")
WS.Range("B:B").Replace What:=".", Replacement:="/", LookAt:=xlPart
End Sub

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

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

Many thanks

Merc

Simon Lloyd
05-22-2007, 10:57 AM
try this after your copy and paste tgt.value = Format(tgt.value, "dd/mm/yyyy")not tested but should do the trick!

mercmannick
05-22-2007, 11:52 AM
Simon

not it is still doing same

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

Merc

lucas
05-22-2007, 11:58 AM
Can't you format your desination range manually...permanantly?

Simon Lloyd
05-22-2007, 12:12 PM
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:

Columns("B:B").Select
Selection.NumberFormat = "dd/mm/yyyy;@"

mercmannick
05-22-2007, 12:16 PM
Lucas

No it still dong same , any help would be appreciated

Merc

lucas
05-22-2007, 12:24 PM
without reconstructing the workbooks you might try not copying...
instead of this:
MyRange.Copy Destination:=tgt
maybe something like this:

tgt = MyRange

lucas
05-22-2007, 12:27 PM
I notice that in SetDate sub you are only replaceing a . with a / what if you format it after you do that?

Charlize
05-23-2007, 12:13 AM
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