Paste values changing dates from 2017 to 2018

[stroffso]

Hi All
I have a piece of code which simply copies data from all cells in one workbook and pastes it into a table in another. However I am finding that one column (Column N) which is in a "MMM-YY" format keeps changing the Year part from 17 to 18 when it paste values over. On the raw file all of column N has "Apr-17" but when it pastes over it comes up as "Apr-18", Has anyone come across this before?

Below is the code I am using for this




With ActiveSheet.UsedRange
LastRow = .Rows(.Rows.Count).Row
End With


Range("A2:N" & LastRow).Select
Selection.Copy
ThisWorkbook.Activate


ColumnA = Range("A10000").End(xlUp).Row + 1
ColumnB = Range("B10000").End(xlUp).Row + 1
ColumnC = Range("C10000").End(xlUp).Row + 1
ColumnD = Range("D10000").End(xlUp).Row + 1
ColumnE = Range("E10000").End(xlUp).Row + 1
ColumnF = Range("F10000").End(xlUp).Row + 1
ColumnG = Range("G10000").End(xlUp).Row + 1
ColumnH = Range("H10000").End(xlUp).Row + 1
ColumnI = Range("I10000").End(xlUp).Row + 1
ColumnJ = Range("J10000").End(xlUp).Row + 1
ColumnK = Range("K10000").End(xlUp).Row + 1
ColumnL = Range("L10000").End(xlUp).Row + 1
ColumnM = Range("M10000").End(xlUp).Row + 1
ColumnN = Range("N10000").End(xlUp).Row + 1
PasteRow = WorksheetFunction.Max(ColumnA, ColumnB, ColumnC, ColumnD, ColumnE, ColumnF, ColumnG, ColumnH, ColumnI, ColumnJ, ColumnK, ColumnL, ColumnM, ColumnN)
If PasteRow = 4 Then PasteRow = 3

'paste
Range("A" & PasteRow).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False

thanks in advance

 

[OGCV1]

Can you post a sample so I can try and replicate?

 

[OGCV1]

Thanks for sending the files over.
The problem comes from copying from a csv file. If you change the Column N dates to the same format as Column C (d-mmm-yy) in the original file it will all copy over okay.

Personally I always try avoid csv.

 

[stroffso]

thanks but Tried that and it doesnt work it still converts them to 2018 dates> I had it before where it worked fine so not sure of how its not working now? Any ideas greatly appreciated

Thanks for sending the files over.
The problem comes from copying from a csv file. If you change the Column N dates to the same format as Column C (d-mmm-yy) in the original file it will all copy over okay.

Personally I always try avoid csv.