I am running a macro to paste data from a 3rd party application into a spreadsheet, but the date format is being changed to American (
mm/dd/yyyy). The regional settings on my PC are for the UK format (
dd/mm/yyyy) and the cells are also formatted as
I have tried using
Range("A1").NumberFormat = "mm/dd/yyyy" before and after the paste line of code, but it does not work.
I have also tried a solution which cycles through all the cells in a range and formats them, but this is too slow (over 10 seconds):
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
Manually using Text to Columns works quickly, but I haven’t been able to integrate it into the VBA code.
Is there any other way to prevent VBA from converting the dates to American format?