Text to Column to resolve date format issue

[clemeb]

I have a weird situation where a macro is copying a large amount of data from one report in to another, some of the data is dates, once copied the date (originally dd/mm/yy) becomes dd/mm/yy hh:mm:ss. It does not recognise it as a format so updating cell format does not resolve it, however when manually running text to columns with no delimeters and format as D/M/Y it resolves my issue. If I add this step in to VBA it never seems to work. But as soon as I do the same manually it works, would anybody know why this is? I have also tried formatting the original data dates before it gets copied to see if that resolves anything with no success. The below is what is not working in VBA:

Please Login or Register  to view this content.

[TMS]

Post a sample workbook.