Thread: Formatting issue with dates

Hi there

having a problem with formatting
column E
this column contains dates(and times) when i open the csv file.
these date's are changed to five digit numbers if i manually edit the format.

but when i run the macro the highlighted line does not change this column(it does change every other column).
the cells in this column become text such as 21.10.2008 15:56.
which is then an issue as i need to filter them when they are number and ultimatly group them in a pivot table.

i recorded the macro from what i was doing and then ran it.
it does work when it is in the module for the vehicle event report.csv

but when run from the other file(as shown below) it encounters this problem.

please help
I usually fix my own code but this has really stumped me.

thanks
Ash

Sub import()
'
' Macro1 Macro
'
 
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
Sheets("data").Select
Columns("A:G").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.NumberFormat = "0.00"
 
ChDir "C:\process\TCO Vehicle Events"
Workbooks.Open Filename:="C:\process\TCO Vehicle Events\Vehicle Event Report.csv"
Windows("Vehicle Event Report.csv").Activate
Cells.NumberFormat = "0.00"
Columns("A:A").Select
Columns("C:C").Select
Columns("D:E").Select
Columns("E:M").Select
Columns("A:H").Select
Selection.Copy
Windows("Event Summary Report.xlsm").Activate
Sheets("data").Select
Columns("A:A").Select
ActiveSheet.Paste
 
Columns("c:C").Select
Selection.NumberFormat = "0.00"
 
Range("c2").Select
 
Do Until ActiveCell.Value = ""
 
If ActiveCell.Value > 9999999 Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
Columns("c:C").Select
Selection.NumberFormat = "m/d/yyyy h:mm"
 
 
 
Windows("Vehicle Event Report.csv").Activate
ActiveWindow.Close
Windows("Event Summary Report.xlsm").Activate
Sheets("Table").Select
 
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
 
End With
 
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.