I am currently using this code to copy from one workbook to another.

Set buswkbSource = Workbooks.Open("J:\business.csv")
Set buswksSource = buswkbSource.Sheets("business")
Set busrngSource = buswksSource.Cells
Set buswkbDestination = ThisWorkbook
Set buswksDestination = buswkbDestination.Sheets("business")
Set busrngDestination = buswksDestination.cells
busrngSource.Copy busrngDestination
Workbooks("business.csv").Close

The problem is that in column "M" of the source workbook is a list of dates.
I am using the UK date format, ie. 5th April 2005 as 5/4/05, unfortunately
the cells are copied using the US date format ie. 4/5/05 or as text if the
date is say 30th April 2005. PLease help!

You should be able to incorporate the code below into your existing code:

buswksDestination.Range("M1").EntireColumn.NumberF ormat = "d/m/yy"

Enter the line after your "busrngSource.Copy busrngDestination".

Hope that helps.

Regards,
James

"ndm berry" wrote:

I am currently using this code to copy from one workbook to another.

Set buswkbSource = Workbooks.Open("J:\business.csv")
Set buswksSource = buswkbSource.Sheets("business")
Set busrngSource = buswksSource.Cells
Set buswkbDestination = ThisWorkbook
Set buswksDestination = buswkbDestination.Sheets("business")
Set busrngDestination = buswksDestination.cells
busrngSource.Copy busrngDestination
Workbooks("business.csv").Close

The problem is that in column "M" of the source workbook is a list of dates.
I am using the UK date format, ie. 5th April 2005 as 5/4/05, unfortunately
the cells are copied using the US date format ie. 4/5/05 or as text if the
date is say 30th April 2005. PLease help!

Thanks for helping. Unfortunately all it does is change 5th April date from
04/05/05 to 4/5/05 ie still in US Format.

"JS2004R6" wrote:

You should be able to incorporate the code below into your existing code:

buswksDestination.Range("M1").EntireColumn.NumberF ormat = "d/m/yy"

Enter the line after your "busrngSource.Copy busrngDestination".

Hope that helps.

Regards,
James

"ndm berry" wrote:

I am currently using this code to copy from one workbook to another.

Set buswkbSource = Workbooks.Open("J:\business.csv")
Set buswksSource = buswkbSource.Sheets("business")
Set busrngSource = buswksSource.Cells
Set buswkbDestination = ThisWorkbook
Set buswksDestination = buswkbDestination.Sheets("business")
Set busrngDestination = buswksDestination.cells
busrngSource.Copy busrngDestination
Workbooks("business.csv").Close

The problem is that in column "M" of the source workbook is a list of dates.
I am using the UK date format, ie. 5th April 2005 as 5/4/05, unfortunately
the cells are copied using the US date format ie. 4/5/05 or as text if the
date is say 30th April 2005. PLease help!