VBA messing up date formats

[rpinxt]

Hi All,
This is the code:

Please Login or Register  to view this content.

The code at first sight does what I wanted it to do. However at a closer look....
So it copies data from a source to a new destination workbook.
Now in this source there are also date and they look like :
30/9/2013
30/9/2013
30/9/2013
1/10/2013
1/10/2013
1/10/2013
2/10/2013
2/10/2013
This is 30 september, 1 october and 2 october which is correct

However after pasting it the dates look suddenly like
30/9/2013
30/9/2013
30/9/2013
10-1-2013
10-1-2013
10-1-2013
10-2-2013
10-2-2013
Now I have 20 september 10 januari and 10 february!!

What happend?! And better yet...how to fix this?
PS: As you can see I already blocked the replacement of "/" to "." and that is how I saw that error arose during the copy/paste action.
Thanks!

[ARGK]

Hi
This looks like you have different date formatting in different cells on your destination worksheet. Make sure that the destination worksheet range is set to the same format as the source cells. The October cells appear to be formatted as US date format.
Good luck.
Tony

[rpinxt]

Hi Thanks, but the destination worksheet is a clean newly generated workbook by the first part of the code.
So would not be possible that the destination sheet has multiple cell fomats in it.

[rpinxt]

Well if I first convert the "/" to "." before I copy/past then I do have the correct dates...but..... Now excel thinks its text.
It will lineout to the left. And as I have in another cell a formula which pulls the weeknumber from that "Date" I get a formula error because it does not see 1.10.2013 as a date.
So if somebody knows how to fix that that also would help!

[rpinxt]

Nobody??
It would also help if someone can tell me vba to format data in several date formats....

[ARGK]

Hi
Can you post a sample of your source worksheet with your code.
Tony

[JosephP]

perhaps replace

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.