Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates Format changing when copying data between workbooks
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 wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates Format changing when copying data between workbooks
Thanks William,
I've tested the idea and it works. I'll adapt your code tomorrow to suit. Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing dates to date format | Excel Discussion (Misc queries) | |||
copying info from one cell to another, changing the format, but leaving the number | Excel Worksheet Functions | |||
Dates keep changing to US format when mail merging | Excel Discussion (Misc queries) | |||
Copying worksheet but changing format | Excel Discussion (Misc queries) | |||
copying workbooks with charts and changing data | Charts and Charting in Excel |