VBA deletes time/date formatting

[Mehow]

Hi all,

I've got macro that does some filtering for me - the problem is pulling the data from sheet1 to sheet2 removes data formatting

example :

ETD	Original ETA to Final Destination	Current ETA to Final Destination
43364	43416	43416
43364	43416	43416
43386	43417	43415

<colgroup><col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <tbody> </tbody>

Sub sort()
   
    Worksheets("Filtered").Range("A2:AZ9999").Clear

    
    Dim shSource As Worksheet
    Dim shDestination As Worksheet
    
    Set shSource = Sheets("Control")    'Source worksheet
    Set shDestination = Sheets("Filtered")    'Destination worksheet
    
    Application.ScreenUpdating = False
                
    'Autofilter column x for "value"
    shSource.UsedRange.AutoFilter Field:=4, Criteria1:=Array("gate out", "sailing", _
                                    "discharged", "arrived", _
                                    "x", "y", _
                                    "z", "aa"), _
                    Operator:=xlFilterValues
    ' Copy filtered data from columns a:z
    shSource.UsedRange.Columns("A:Z").Offset(1).Copy
    
    'Paste the filtered data to Sheet "x"  column Dx
    shDestination.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    'Clean up
    shSource.AutoFilterMode = False
    Application.Goto shDestination.Range("A1")
    Application.ScreenUpdating = True
    MsgBox "Completed"
    
End Sub

Any idea why?

 

[SpillerBD]

trade "xlPasteValues" for "xlPasteValuesAndNumberFormats"

 

[Mehow]

Thanks! works