excel vba activateworkbook.save changes date format

There is an issue where excel needs to notice formats when you save to CSV through VBA.

We can get around it multiple ways. We could write the file more complicated ways, but one which you will likely prefer is seeing the workbook copy over.

I've taken the liberty of making a new macro for you which follows some better practice. "Activeworkbook" could pickup the wrong file for example. I hope this helps.

Public Sub CreateNewCsv()

    Const FileNamePrefix As String = "CSV Export "
    Const PickupSheet As String = "InformationSheet"
    Const PickupRange As String = "A1:B2"
    Const DateFormatWanted As String = "dd-mm-yyyy"

    Dim FilePath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim arr As Variant
    Dim iRow As Long
    
    On Error GoTo ErrHandler
    
    ' Get Filepath to save new document
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder to save CSV"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count = 0 Then GoTo Ending Else FilePath = .SelectedItems.Item(1)
    End With
    
    ' Create a filename
    FileName = FileNamePrefix & Format(Now(), "YYYYMMDDHHNNSS")
    
    ' Create new workbook and get the first sheet
    Set wb = Workbooks.Add
    Set ws = wb.Worksheets(1)
    
    ' Grab our date data
    arr = ThisWorkbook.Sheets(PickupSheet).Range(PickupRange).Value2
    
    ' Convert the dates to string
    '   This will only change the first column in this example
    For iRow = 1 To UBound(arr, 1)
        arr(iRow, 1) = "'" & Format(arr(iRow, 1), DateFormatWanted)
    Next iRow
    
    ' Input the values to our new workbook's worksheet
    ws.Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        
    ' Save the workbook as a CSV
    wb.SaveAs FilePath & Application.PathSeparator & FileName, xlCSV
    wb.Close False

    GoTo Ending
ErrHandler:
    MsgBox "Unexpeced error during 'CreateNewCsv'", vbExclamation
Ending:
End Sub