VBA Incorrect Date Format When Saving As .csv

[ir121973]

Hi, I wonder whether someone may be able to help me please.

I'm using the code below to copy a selected range of cells, create a new workbook and then paste the data into sheet 1 from the source to the recipient sheet.

Sub copysheet()
    Application.ScreenUpdating = False


    Dim Src As Worksheet, r As Range
    Dim LastRow As Long
    Dim MyString As String
    Dim wkb As Workbook


    MyString = "SC, DV"
    ary = Split(MyString, ",")


    Set Src = ThisWorkbook.Sheets("Amalgamation of Search")
    LastRow = Src.Cells(Cells.Rows.Count, "C").End(xlUp).Row
    Set r = Src.Range("C2:C" & LastRow)


    With r
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
    End With


    Src.Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    Set wkb = Workbooks.Add
    Sheets("Sheet1").Range("A1").PasteSpecial xlValues
    Src.Range("C1:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    wkb.Sheets("Sheet1").Range("B1").PasteSpecial xlValues
    Src.Range("D1:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy
    wkb.Sheets("Sheet1").Range("C1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Src.AutoFilterMode = False
    ActiveWorkbook.SaveAs Filename:= _
        "\\c\s\CAF1\Digital Delivery Group\DDCOPS\Data Security\SC Data.csv", _
    FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Workbooks.Open Filename:="""\\c\s\CAF1\Digital Delivery Group\DDCOPS\Data Security\SC Data.csv"
    ActiveSheet.Range("C2:C" & LastRow).Select
    Selection.Replace what:=",", replacement:="", Lookat:=xlPart
    Application.ScreenUpdating = True
End Sub

The macro copies and pastes the data without any issue except for a date column (Column C) in the recipient sheet.

The source sheet date column is dd/mm/yyyy, the format when the data is copied to the recipient sheet becomes dd-mm-yyyy. However, I need this to be in the format "dd mmm yyyy" because I'm importing this into a third party application which doesn't not accept forward slashes.

Over the last few days I've tried so many ways to try and get this to work e.g. using the format (ddd, mmm, yyyy) then removing the commas. I've then tried paste this as text, but I can't get this to work. I've then used the 'pastevalues' method which again doesn't provide the format I need.

I just wondered whether someone could take a look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

 

[yky]

Did you try TEXT(C1,"dd mm yyyy")?

 

[ir121973]

Hi @yky, thank you for coming back to me with this.

Could you perhaps explain how I would incorporate this into my macro please?

Many thanks and kind regards

Chris

 

[yky]

Which cell or cells contain date?

 

[ir121973]

Hi yky, thank you for coming back to me with this.

I've managed to sort this by using the format "mmmm dd yyyy".

Thank you for all your help and regards

Chris