This is driving me nuts... Dates deformated

Eshton

New Member
Joined
Aug 22, 2005
Messages
31
Using office 2003. Attempting to copy a range of cells from one file (closed) to an other file. Works ok but the dates (if under 12..) are re-formated into the American format i.e. copy 06/01/2006, paste 01/06/2006. Looks like if over 12. Excel logic prevails so dates stay on the same format. This does not happen if this is done manually, only happens when using VB. Has anyone come accross this?

The code I use is:
GETValuesFromClosedWorkbook "C:", ("D4"), "Sunbatch", "A1:R3000"
Columns("T:T").Select

However, even a simple copy / paste instruction via VB seems to give same wrong outcome.

This is driving me nuts.
If you can, please-please help.

Many thanks,
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
Eshton,

Check the destination cells for the correct formatting first. If they are general, change them to Date of some kind. Next, use Paste Special Values in your macro so you don't pick up any formatting from the source cells. Does this solve the problem?

If not, try creating a dummy workbook from scratch and populate it with dates. Create another dummy workbook from scratch and use it as the destination workbook. Does the problem still occur?

Dufus
 

Eshton

New Member
Joined
Aug 22, 2005
Messages
31
Thanks Dufus for the prompt reply.

Have tried your suggestion (amongst many things I have tried) but I'm getting the same result. It does'nt make sense. Will appreciate any other ideas.

Many thanks,
Eshton
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
This is the kind of thing it's really difficult to decipher without having a copy of the suspect workbook. I'm out of ideas for the time being though.

Dufus
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top