Dates Format changing when copying data between workbooks

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.

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
[email protected]


<[email protected]> wrote in message
news:[email protected]...
> 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.
>

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