Exporting Selected cells to new CSV keeping formatting

[Lewis101]

Hi All,

im trying to export cells A2:IU20 in sheet 'StaffTB' to a new CSV file. this works perfectly however Columns F and G need to keep the formatting of yyyy/mm/dd hh:mm:ss, i thought i had it fixed by adding .PasteSpecial xlPasteFormats but this exports it with dd/mm/yyyy hh:mm

Sub ExportStaffTB()


    Dim myCSVFileName As String
    Dim myWB As Workbook
    Dim tempWB As Workbook
    Dim rngToSave As Range


    Application.DisplayAlerts = False
    On Error GoTo err


    Set myWB = ThisWorkbook
    myCSVFileName = "C:\Delete\StaffTB" & ".csv"


    Set rngToSave = Range("A2:IU20")
    rngToSave.Copy


    Set tempWB = Application.Workbooks.Add(1)
    With tempWB
        .Sheets(1).Range("A1").PasteSpecial xlPasteValues
        .Sheets(1).Range("A1").PasteSpecial xlPasteFormats
        .SaveAs filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
        .Close
    End With
err:
    Application.DisplayAlerts = True
End Sub

 

[Fluff]

As far as I know, csv files cannot save formats.
To maintain the format you want, you'd probably have to change the dates to text first

 

[Lewis101]

Worked a treat thank you, i didnt even consider that a CSV wouldnt save formats.

 

[Fluff]

Glad to help & thanks for the feedback