Dates Format changing when copying data between workbooks

[mcnaught]

Hi I am copying data from one sheet and pasting it into another
workbook.

Range("A1").Select
Selection.CurrentRegion.Select
Selection.Delete
Rows("1:1000").Select
Selection.Delete
Workbooks.Open Filename:="p:\pro65\csr01.xls"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
ActiveWindow.Close
Range("A1").Select

An example of the dates Before and After follows:
Note I require DD/MM/YYYY format
Note some cells have " - -"
BEFORE
- -
- -
1/11/2004
18/10/2004
10/08/2004
15/12/2004
10/12/2004
13/12/2004
12/11/2004

AFTER
- -
- -
11/01/2004
18/10/2004 PROBLEM
8/10/2004 PROBLEM
15/12/2004
12/10/2004 PROBLEM
13/12/2004
11/12/2004 PROBLEM

The problem:
If DD < 12 the the format changes from DD/MM/YYYY to MM/DD/YYYY

PS if I copy and paste the data manually I don't get the problem.

 

[William]

Before you do the copying, format the cells in "csr01.xls" to
"dd/mmm/yyyy" -once the cells have been copied reformat them in the
destination workbook.to dd/mm/yyyy

Untested, but your code could be tightened up to something like..

Sub test()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("A1").CurrentRegion.Delete
ws.Rows("1:1000").Delete
Workbooks.Open Filename:="p:\pro65\csr01.xls"
With Workbooks("csr01.xls").Sheets("Whatever")
..Range("A1").CurrentRegion.NumberFormat = "dd/mmm/yyyy"
..Range("A1").CurrentRegion.Copy ws.Range("A1")
End With
Workbooks("csr01.xls").Close savechanges:=False
ws.Range("A1").CurrentRegion.NumberFormat = "dd/mm/yyyy"
ws.Range("A1").Select
End Sub


--


XL2003
Regards

William
(e-mail address removed)

 

[mcnaught]

Thanks William,
I've tested the idea and it works. I'll adapt your code tomorrow to
suit.
Peter