Date format chaging when copying sheet from another workbook

[harshad_agashe]

Hi,

(its nice to see mrexcel forum still active. last time I used it was in 2002 ! )


I am trying to open a different workbook and copy one of sheets data to current sheet. It works, but date stored as "1/11/2013" is chaging to "11/1/2013". (if the date is "23/9/2013", its getting copied correctly. probably because background format conversion does't produce valid date).

The copy of the sheet works, just the date format changes.

I tried pasteSpecial but didn't help.

Macro is as below.


Sub read_file()
Dim oWB As Workbook
Dim x_rrc_extract As String


x_rrc_extract = "C:\Temp\rrc_extract.xlsx"
Set oWB = Workbooks.Open(x_rrc_extract)


'Original code to copy paste sheet
With ActiveSheet
.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With

Range(Selection, Cells(1)).Select
Selection.Copy


Application.DisplayAlerts = False
oWB.Close
Application.DisplayAlerts = True



' pasting the selection from source sheet to the target
Windows("Book1.xlsm").Activate
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Save

'New code added on 3/Sep to take control to first sheet
Sheets("Sheet1").Select
Range("A1").Select
End Sub