Copy and pasting with VBA nullifies formatting

Question

I've written some VBA to copy and paste some data from one workbook into another:

Dim x As Workbook
Dim y As Workbook

' Open both workbooks
Set y = ActiveWorkbook
Set x = Workbooks.Open("Data.csv")

' Copy data from x
x.Sheets("SourceData").Range("A1", _
    x.Sheets("SourceData").Range("A1").End(xlDown).End(xlToRight)).Copy

' Paste to y
y.Sheets("Destination").Range("C4").PasteSpecial Paste:=xlPasteValues
y.Sheets("Destination").Range("C4").PasteSpecial Paste:=xlPasteFormats

This pastes the data in the correct location.

The next step of the VBA is to filter the data table by the first column which contains a date. However, the paste of the data converts everything to general data type, but more importantly seems to lose the 'date value' behind the string in the field. For example, when I try to format the date column following the paste, no other format type will change what appears in the cell (i.e. converting to number will still show 01/01/2018 rather than 43101). This causes the filtering code to hide all rows because there are no dates that fall within the parameters (because there are no dates essentially). I can't even manually filter on the dates (i.e. without VBA).

This image shows how the preview for each data type is still 20/02/2018.

Whenever I manually copy and paste the data it works fine and I can format the dates in any way. It's just when using VBA that the formatting issues crop up.

Amongst other things I've tried:

Using VBA to format the columns in the source sheet before copy/pasting
Using VBA to format the columns in the destination sheet after copy/pasting
Moving the whole sheet into the destination workbook and copying/pasting from within the workbook (loses formatting upon moving sheet)
Pasting values & then pasting format over the top
Using different file formats for the data file

I wonder if the issue could be due to a setting in Excel? Any help greatly appreciated.

My suspicion is that your Windows Regional Short Date settings are a variant of MDY, that the data in your workbook Data.csv is formatted as text; and that you OPEN'd instead of IMPORTing the original csv file so you never had the opportunity to properly format the dates. If that is the case, we have to go back to the source csv file. — Ron Rosenfeld

DisplayName DisplayName · Accepted Answer · 2018-02-28T17:04:38

along the lines of @tigeravatar solution, but with a more concise code

Dim y As Workbook

Set y = ActiveWorkbook

With Workbooks.Open("Data.csv").Sheets("SourceData") 'open source workbook and reference its "SourceData" sheet
    With .Range("A1").CurrentRegion 'reference referenced sheet range "adjacent" to cell A1
        y.Sheets("Destination").Range("C4").Resize(.Rows.Count, .Columns.Count).value = .value
    End With
    .Close False
End With

Copy and pasting with VBA nullifies formatting

2 Answers