I have a macro that grabs all of the data from a CSV file which is generated by another program.(Below is Macro I recorded, It opens the CSV file copies all of the cells, goes to the main workbook, pastes the info, then closes the CSV file.)
Column C in the CSV is a DATE in the format "dd/mm/yyyy". The macro works great except the DATE does not get copied over as a date. It looks the same, in the same order, it's left justified. If I select the cells and chose DATE from formatting, nothing happens. I've tried Paste as Values, and PasteSpecial. If I manually go into edit the cells contents and just hit enter, it automatically recognizes as a date and sets it to right justified and the formulas in the other sheets work fine. How do I get the pasted data to be recognized as a Date value?
Column C in the CSV is a DATE in the format "dd/mm/yyyy". The macro works great except the DATE does not get copied over as a date. It looks the same, in the same order, it's left justified. If I select the cells and chose DATE from formatting, nothing happens. I've tried Paste as Values, and PasteSpecial. If I manually go into edit the cells contents and just hit enter, it automatically recognizes as a date and sets it to right justified and the formulas in the other sheets work fine. How do I get the pasted data to be recognized as a Date value?
VBA Code:
Workbooks.Open Filename:= _
"C:\Raw Data Worksheet.csv"
Cells.Select
Selection.Copy
Windows("Tally Worksheet.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Application.WindowState = xlNormal
Windows("Niagara Paint Worksheet.csv").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWindow.Close