Copying cells from one workbook to another.

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.NumberFormat = "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.NumberFormat = "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!
> >