Trouble with code

[Kell2Jam]

I think this code is incorrect, it seems to be pasting the the dates the wrong way around and i know that its something simple but i cant figure it out.

Workbooks.Open "P:\FETS Command\Business Services\STORES OFFICE\James - Work\#LineItemsReport\PurchaseOrders.csv"
Windows("PurchaseOrders.csv").Activate
Dim wsCopy As Worksheet
'Dim wsDest As Worksheet
'Dim lCopyLastRow As Long
'Dim lDestLastRow As Long
'Dim strPath As String

Set wsCopy = Workbooks("PurchaseOrders.csv").Worksheets("PurchaseOrders")
Set wsDest = Workbooks("LineItemsReportGraph.xlsm").Worksheets("Purchase Orders")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Clear contents of existing data range
wsDest.Range("A1:K" & lDestLastRow).ClearContents

'4. Copy & Paste Data
wsCopy.Range("A1:K" & lCopyLastRow).Copy _
wsDest.Range("A1")


Workbooks("PurchaseOrders.csv").Close SaveChanges:=True

 

[Mentor82]

Hi,
What exactly do you mean by:”it pastes the wrong way around”? You mean it pastes values without formatting or sth else?

 

[Kell2Jam]

Well when copying from another workbook the date is set at DD/NN/YYYY but when it paste to the worksheet i need it to, it seems to switch to MM/DD/YYYY.
But when checking the format it shows it as DD/MM/YYYY.

 

[MARK858]

If you use VBA to extract dates from a range in dd/mm/yyyy format into VBA then they are converted to US dates (i.e mm/dd/yyyy).

If you are only interested in the values (no formatting, no formulas) then try the below and format the date column as dates. If you want either the formatting or formulas then it gets more complicated.

For values then replace

wsCopy.Range("A1:K" & lCopyLastRow).Copy _
      wsDest.Range("A1")

with

    With wsCopy.Range("A1:K" & lCopyLastRow)
        wsDest.Range("A1").Resize(.Rows.Count, .Columns.Count) = .Value2
    End With

P.S. can you please use code tags in future when posting VBA code

 

[Kell2Jam]

No Problem ill be sure to use tags next time.

I tried replacing the code but i got the same result

 

[Norie]

Have you checked to see if the formatting is the problem? i.e. the correct values are being copied but they aren't formatted correctly

Also, are the dates in the source file actually 'real' date values?

 

[MARK858]

Also on top of what Norie has asked are you sure that your Windows regional date settings are dd/mm/yyyy?

 

[Kell2Jam]

Norie - Yeah i checked all that and they are all correct and yes they are real date values.

MARK858 - How would i check that?

 

[Kell2Jam]

 

[MARK858]

That looks fine (although click additional settings - Date and see if it matches there).
In your wsCopy workbook if you in a blank cell put the formula =ISNUMBER(A2) changing the A2 to a cell reference with a date do you get TRUE or FALSE?