VBA converting UK date format (dd/mm/yyyy) to American format mm/dd/yyyy) when pasting data

I am running a macro to paste data which has already been copied onto the clipboard into a spreadsheet.

The data is copied from a 3rd party application. When pasted manually the first column is date in UK format (dd/mm/yyyy), the rest of the columns contain both text and numeric data types. The data contains text headings.

However, when I run the paste macro the format of the date is being changed to American (mm/dd/yyyy).

• The regional settings on my PC are for the UK format (dd/mm/yyyy)
• The cells are also formatted (dd/mm/yyyy)

Assuming I am pasting the data in cell A1 (as in my table starts in cell A1). This is effectively my code:

Range("A1").PasteSpecial xlPasteAll

Is there anyway to prevent VBA from converting the dates to American format?

Just a note, the issue only occurs when copying dates from outside of Excel. And also only when the day value (dd/mm/yyyy) is 12 or smaller as VBA can't convert any day bigger the 12 into a month.

For example:

Copying: 11/06/2023
Returns: 06/11/2023

however,

Copying: 13/06/2023
Returns: 13/06/2023

Excel Version: Office 365

I've tried using Range("A1").NumberFormat = "mm/dd/yyyy" before and after the paste line of code with no success.

This works...
' Assuming A1 is my "Paste_Cell"
' A1 is the date column heading "Date"
' A2:A3000 is where the date values are

Dim DateRange As Range

Range("A1").PasteSpecial xlPasteAll
Set DateRange = Range("A2:A3000")
For Each Cell in DateRange
Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
Next Cell

However because this is having to cycle through 3000 cells its slowed the paste macro from 1-2 seconds to over 10seconds!

Using text to columns manually does the trick nicely and quickly, however I haven't been able to integrate that into my VBA code and get the macro working. Unfortunately doing the text to columns method manually isn't a viable option for the application.

Any better solutions welcome!

Try pasting into a text column and then converting with dateserial

Sub pastedata()

    Dim lastrow As Long, i, ar, arData, t0 As Single
    t0 = Timer
    With ThisWorkbook.ActiveSheet
       .Range("A:A").NumberFormat = "@"
       .Range("A1").PasteSpecial
       lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
       arData = .Range("A2:A" & lastrow)
       For i = 1 To UBound(arData)
          ar = Split(arData(i, 1), "/")
          arData(i, 1) = DateSerial(ar(2), ar(1), ar(0))
       Next
       .Range("A:A").NumberFormat = "dd/mm/yyyy"
       .Range("A2:A" & lastrow) = arData
    End With
    MsgBox lastrow - 1 & " rows pasted in " & Format(Timer - t0, "0.00 secs")
       
End Sub

</details>