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:



Columns("X:X").Select
Selection.TextToColumns Destination:=Range( _
"Table1[[#Headers],[Packset-Country Marketed Date]]"), DataType:=xlDelimited _
, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True